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

[@DougHExcel]

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

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

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

[@chrisklimantiris2520]

Thank you Mike..!!

[@excelisfun]

You are welcome!!!

[@bitechmacrobitechmacro5066]

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. :))

[@excelisfun]

Yes, you can use IN in Power BI Desktop. All the DAX Formulas and Functions that I show at the excelisfun channel can be used in Excel Power Pivot or Power BI Desktop!!!

[@johnmatta9577]

very well explained Mike

[@excelisfun]

Glad you like it!

[@himanshudalai1028]

Superb one ! Thank you Mike !!

[@excelisfun]

Glad it was superb. This was a fun one to do with the IN Operator : )

[@vida1719]

Great video. Learned something new

[@excelisfun]

Glad it was helpful!

[@ikar2k]

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?

[@excelisfun]

To load NOT to sheet but to Data Model in Power Pivot, in the Import data dialog box, click Connections Only AND Add to Data Model.

[@excelisfun]

I made a mistake in my last post. Power Pivot does not have a direct way of creating a table. Power BI Desktop does, but not Power Pivot. I have not done this before, but I think that "round tripping" is possible, but it means you would creating it in the sheet and then import it back into Power Pivot, either with Power Query or the import table feature in Power Pivot.

[@ikar2k]

ExcelIsFun It's shame :( It would be great to make virtual tables on base of queryes (like views in sql) from another tables in PP.. MS, we will wait :)

[@excelisfun]

Yes, it is a shame, especially since it is so easy to do in Power BI Desktop... I Think you are right, MS will probably add it soon, so we wait... ... ...

[@ikar2k]

ikar2k Or may be feature of wraping dax instruction in connection directly (in moment of creating PT)..

[@hosseinhosseinpoor9561]

خیلی عالی

[@CurbalEN]

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

[@excelisfun]

What!?!?!? You can't fire her!!! She is rad, she is part of the Team : ) Plus, maybe she got it right, but you just temporarily misunderstood?

[@excelisfun]

Or maybe she got it exactly right, but she misspoke when she communicated her results to you?

[@CurbalEN]

Either way, she is fired! I will test my waters somewhere else...Who knows I might guess every video you make! I have a huge backlog though: 1437 against my poor 38... I need more hours in a day! /Ruth

[@excelisfun]

You have way more than 38 videos, don't you? I don't see a way at your channel to see your total video count, but it seems like you have 100s of videos!?!? Even if you have 38, I think you mis-typed: I think you meant "my EXCELlent 38" : )

[@CurbalEN]

Yes, yes I have 150 to be exact ;) but on DAX I have only 38... seems like nothing comparing to your number count! You can see the number of videos here: socialblade.com/youtube/channel/UCJ7UhloHSA4wAqPzyi6TOkw /Ruth

[@vladimirkosimovsky6622]

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

[@excelisfun]

I am not an expert yet with bottlenecks and performance, yet. This is mostly because in order for issues to come up you need big data and I just don't have much big data. I am learning on both fronts, so hopefully soon I will be able to make such videos. What issues have you run into, and what sort of data and how big is your data?

[@tariq313313]

Brilliant.

[@excelisfun]

Glad you like it!!! Nothing like a few good pictures to help in understanding : ) Thanks for the support, DDDD, with your comment, Thumbs Up and Sub!

[@mehmetatas5459]

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

[@mohamedchakroun4973]

1000* THUMBS UPPPPPPP

[@excelisfun]

Thank you for the 1000 Thumbs Up!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[@mehmetatas5459]

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

[@excelisfun]

IN is only in the latest version of Excel and Power BI Desktop - you have to have the version that is connected online and updates each month. Since Microsoft has many versions these days, it really depends on the version.

[@crossett10]

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!

[@crossett10]

Office Version 1701 (Build 7766.2099)

[@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."

[@excelisfun]

I do not know how to fix that. Also, I am fairly sure that IN does not work in earlier versions. You have to have Power BI Desktop or a 365 version of Excel that has the ability to be updated each month.

[@friklloyd2716]

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))

[@excelisfun]

Yes!!!! That is a beautiful formula : ) If you don't have IN, maybe you have an older version. What version do you have?

[@friklloyd2716]

Excel 2016

[@friklloyd2716]

IN works in Power BI Desktop

[@excelisfun]

Yes, Power BI Desktop is always ahead of Excel and Power Query...

[@excelisfun]

Yes, the version that i have is Excel 2016, Office 365, Insider Edition, Slow.