Multiple Filters OR on multiple columns from multiple tables

[DeeBali]

Hello Fabric Community,

I need some assistance in solving a calculation I am attempting. So I am trying to count Name column in one of my tables called "MasterList" based on filters across multiple tables and columns (that all i have relationships to each other). However the logic I need to use is that the calculation counts if either of the filter is true, not when every single filter is true. Here is the code I am using:

NON COMPLIANT = calculate(distinctcount(MasterList[Name]), AD[In SCCM]="No",SCCM[In AD]="No",Defender[Onboarded]="No",Purview[Onboarded]="No", InTune[Onboarded]="No",MasterSecureBaseline[State] ="Yes")

So this gives me a number based on all these conditions being true (which is much lower number than expected). What I am trying to do is count Name column in MasterList table when any of the filters is true (like a OR logic). Please help me! Thank you team! 

[lbendlin]

use ||  or IN   function to achieve that.

[DeeBali]

Hello,

Thank you for your response. I have not used IN funtion prior to this, may I get some hint on how to write this statement? Thank you. 

[lbendlin]

It doesn't apply in your scenario.  You would use the || operator

NON COMPLIANT = calculate(distinctcount(MasterList[Name]), 
                                  AD[In SCCM]="No" || 
                                  SCCM[In AD]="No" || 
                                  Defender[Onboarded]="No" || 

                                  Purview[Onboarded]="No" || 

                                  InTune[Onboarded]="No" || 

                                  MasterSecureBaseline[State] ="Yes")

You may need aggregations - depends on your setup.

[DeeBali]

Hello,

I tried this code and the errorI recieve is below:

"The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression."

[lbendlin]

Fair enough.  In that case do a distinctcount over the Union of these tables.

NON COMPLIANT = distinctcount(union(
                                  Filter(Values(MasterList[Name]),AD[In SCCM]="No"), 
                                  Filter(Values(MasterList[Name]),SCCM[In AD]="No"),
                                  Filter(Values(MasterList[Name]),Defender[Onboarded]="No"),

                                  Filter(Values(MasterList[Name]),Purview[Onboarded]="No" ),

                                  Filter(Values(MasterList[Name]),InTune[Onboarded]="No"),

                                 Filter(Values(MasterList[Name]), MasterSecureBaseline[State] ="Yes")
                                ))

[DeeBali]

Sir, this is the error I recicved:

[lbendlin]

fair enough

NON COMPLIANT = countrows(distinct(union(
                                  Filter(Values(MasterList[Name]),AD[In SCCM]="No"), 
                                  Filter(Values(MasterList[Name]),SCCM[In AD]="No"),
                                  Filter(Values(MasterList[Name]),Defender[Onboarded]="No"),

                                  Filter(Values(MasterList[Name]),Purview[Onboarded]="No" ),

                                  Filter(Values(MasterList[Name]),InTune[Onboarded]="No"),

                                 Filter(Values(MasterList[Name]), MasterSecureBaseline[State] ="Yes")
                                )))

[DeeBali]

Hello Sir, thank you for your assistance. Witht his new DAX commands, I get below error:

And for clarification, I did chnage the table name from MasterSecureBaseline to SBMissing as that is table I need to use. I I did do summarization on the State column in the SBMissing table and others as well:

Thank you. 

[lbendlin · Accepted solution]

I will have to ask you to provide samples of the MasterList table and at least two of the other participating dimension tables.

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[DeeBali]

Hello,

Thank you. I was able to get desired results by calculating a SUM on each measure from the table and made a new measure. Thank you for your help.