Excel Magic Trick 1440: OR Logical Test on 2 Columns using DAX IN Operator & DAX OR Function

Download File:
Start File: excelisfun.net...
Finished File: excelisfun.net...
Entire page with all Excel Files for All Videos: excelisfun.net
See how to perform an OR Logical Test on Two Columns using the DAX IN Operator and the DAX OR Function. The goal is to create a Data Model PivotTable with Row Area Criteria that can flow into the DAX Measure for adding with on OR Logical Test on Two Columns. See the DAX Functions: CALCULATE, SUM, FILTER, ALL, IN, OR, and ROW. See how to use DAX Studio to Build & Format DAX Formulas, and to visualize DAX Formula elements to learn how the formula evaluated. Learn about using ALL Function on columns rather than the whole table in order to allow formula to iterate over a smaller number of rows. These formula work in Power BI Desktop and Excel Power Pivot.

[@vida1719]

Mike, you are great Teacher, explaining so clearly! I prefer IN operator as it looks neater and less typing.

[@excelisfun]

Thanks for your comment and support : )

[@Sal_A]

Thanks for that lucid explanation using DAX Studio. Breaking DAX in small chunks makes it easy to understand. I like the IN operator bc it's more succinct but the OR DAX function is more Excel like.

[@excelisfun]

Yes, IN is like SQL and OR is "more Excel like" : )

[@DougHExcel]

Nice example on using IN / OR DAX functions...thank!

[@gilems9]

Wow, Excel is really creeping up on SQL with DAX. With a few shortcomings, Excel looks more and more like a database; for analyzing anything less than a few million records, Excel is becoming a very good tool! exciting! Also, Mike at 12:27 your video Shows "Crtl + Z" when it should say "Crtl +V"

[@excelisfun]

Thanks for the note about "Crtl + Z" when it should say "Crtl +V"... I can no longer add annotations to the video to add a note about the incorrect keyboard because KZbin took that feature away. Power Query is very much like SQL. The Data Model and DAX is only a very small amount like SQL. However, with the Excel or Power BI Data Model, you can analysis 100 Million rows with ease and a Billion or so rows with careful model construction. Much more than a few million...

[@gilems9]

Damn, that's amazing... about a billion rows!

[@excelisfun]

Yes, it's all about the speed of the Columnar Database and DAX, both of which are original inventions by Microsoft.

[@armondnazarian4455]

Excellent, I think I go with IN method.

[@excelisfun]

IN : )

[@mohamedchakroun4973]

No Comment Mike absolutately great what you are doing I like the IN Method it is easier than the OR Method but we have to khnow the two ways Teacher I am looking forward to more dax videos..

[@excelisfun]

Many more are coming near the end of June and into summer!!!

[@mohamedchakroun4973]

ExcelIsFun Waw great looking forward :-)

[@hosseinhosseinpoor9561]

خیلی عالی

[@duafm]

I need to Filter a list with 20 items. I need 18 of them. How do i filter only 2 or i need to filter 18 to exclude 2. Any Good way to do that? I got this one but its not working:- NOT ( table[column] IN { "item 1", "item 2" } ) are we gonna use with routine filter or what?

[@rajkumarrajan8059]

can we use it for text values?

[@pravinambhir1496]

Hi Mike Thank you for all the excel videos. It's very useful. Would you know how to recover corrupt excel file?

[@saddamthabit]

I was wondering why in DAX there is no WORKDAY.INT function and how to go around that

[@excelisfun]

In general, counting work days is a count row calculation, where a Calculated Column in the Date Table contains a marker for what days are work days.

[@ljubicar1987]

OR solution is easier to me. IN is more for Dax ninjas.😊

[@excelisfun]

TRUE : )

[@daramawati]

Great videos as usual!When i download your excel file in to my Excel 2016 Swedish version i cannot Edit any DAX i get error message ? MdxScript model 6,158 xxx......

[@excelisfun]

It may be that the IN Operator has not made it into your version yet... The way that Microsoft updates different versions at different times, is really annoying. But in a few years once MS gets it all figured out, we all should be on the same time schedule.

[@daramawati]

Thank you for your quick answer , King Of excel ;)

[@ahmedomar-sw3kv]

Hi man, hope my message will find you well. I need your help in an issue about Dax measures, how can I communicate with you if you would help, please. thanks in advance

[@himanshudalai1028]

Beautifully explained. Thank you so much Mike !! Is there any reason why "IN" operator is not working in my case. Very sad case. I had to take resort to the 2nd option which worked fine.

[@excelisfun]

IN is not in all versions. What version do you have

[@himanshudalai1028]

Microsoft Office Professional Plus 2013

[@excelisfun]

Excel 2016 or Office 365 has it. Power BI Desktop also.

[@himanshudalai1028]

Thank you so much for the information.

[@excelisfun]

You are welcome, HIMANSHU!!!