Excel Magic Trick 1437: DAX Query OR Logical Test IN Operator: Pull Sales Team Data to Excel Sheet

Download File:
See how to build a DAX Query OR Logical Test using the IN Operator to Pull Sales Team Data to an Excel Sheet from the Power Pivot Data Model. See the DAX Functions: CALCULATETABLE and VALUES. Use DAX Studio to build the DAX Query and then use Excel’s Existing Connections, Edit DAX Feature to connect to the Data Model and pull data to an Excel Sheet.

[DougHExcel]

DAX Studio...neat tool. Thanks for the video!

[АлександрДенисов-зф]

Really thanks! That's exactly what I need now, doing my Project at work.

[bitechmacrobitechmacro]

Woww, I used IN in SQL, but Because of not listing Power Pivot, i didnt try it. I always used OR. So, its highly possible that in Power BI Desktop, we can use IN also. :))

[mehmetatas]

Thanks Mike How can I do the same OR function with the OR function. Could you give me the function formula.

[vladimirkosimovsky]

Thanks for video! please, more videos about using DAX Studio for analysis of bottlenecks and performance, if it possible

[ikark]

Thank's a lot for so clear explanation technology of using DAX. It's awsome!
Here raise the question: how to exclude a loading data in excel sheet? How to make a table straight in PoverPivot?

[CurbalEN]

Great example for both CALCULATETABLE and IN. I am firing my psychic or perhaps I am paying her too little....
/Ruth

[mehmetatas]

Hi Mike, I am having trouble with DAX Studio IN function.

[crossett]

Hi, I have carefully tried to do this workbook myself from the supplied start file and get an ongoing error.    Also, after switching to the supplied finished file, when I try to refresh the OR table the output table doesn't update, I believe due to the same error.

Information on my circumstances:
1) I have checked and I have the latest version of Excel 2016 as part of Microsoft Office 365 ProPlus
2) The specific error I see is from Dax Studio is: "Query (2, 30) A function 'CONTAINSROW' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
3) Specific code in Dax Studio and in the finished excel file is:
EVALUATE
CALCULATETABLE(AllSalesTable, dSalesRep[SalesRep] IN
VALUES(disTeamNames[OR Criteria]))
4) In regards to above there is a blank after the "IN" function

Anyone have the same issue? Any thoughts on what is causing this error? Thanks in advance!

[Sal_A]

I couldn't open the start file data model in Power Pivot.  When I click the Power Pivot Window icon using Excel 2010 I get an error that says "this workbook contains an Excel data model that is created in a newer version of Excel."

[friklloyd]

Hi Mike
IN won't work for me (don't know if there is some setting) so therefore i created a one to many relationship of the paramtable dTeamnames field Team and AllSalesTable field SalesRep
The Dax Formula is as follows:-
EVALUATE
CALCULATETABLE(AllSalesTable, RELATEDTABLE(dTeamNames))