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 60 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 4 guests