Replicating the Behaviour of the IN Keyword in Excel 2013 DAX

Anything related to PowerPivot and DAX Formuale
sam
Posts: 3
Joined: Mon May 05, 2014 6:35 pm

Replicating the Behaviour of the IN Keyword in Excel 2013 DAX

Postby sam » Tue Jan 23, 2018 1:33 am

@Matt
Can the below measure be replicated in Excel 2013

CALCULATE([mFTE],
FILTER(D,D[DEPT] IN
SELECTCOLUMNS(
FILTER(LOGNM,LOGNM[USERID]=[mUserNm]),
"DEPT",LOGNM[DEPT])))

Here D is a Fact table with Columns
OPPNO YEAR FTE DEPT
And LOGNM is a table of Login names with colunms
USERID ROLE DEPT

Also it is possible to define a Role in Power BI Desktop that says DEPT = List of Values returned by a FILTER(D,D[DEPT] IN
SELECTCOLUMNS(
FILTER(LOGNM,LOGNM[USERID]=[mUserNm]),
"DEPT",LOGNM[DEPT]))

I have attached an Excel file with the Data model and the measures - I am desperately trying to get this to work in Excel 2013 as the Client is still on 2013 with no plans to upgrade to 2016
Attachments
TEST.xlsx
(219.62 KiB) Downloaded 6 times

matthew
Posts: 14
Joined: Sun Apr 17, 2016 5:01 pm

Re: Replicating the Behaviour of the IN Keyword in Excel 2013 DAX

Postby matthew » Wed Jan 31, 2018 10:39 am

This article may help:

https://www.sqlbi.com/articles/from-sql-to-dax-in-and-exists/

Excel 2016 added the IN operator, but for 2013 you have to look elsewhere.

Matt


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 10 guests