topic Re: Multiple Filters OR on multiple columns from multiple tables in DAX Commands and Tips

Multiple Filters OR on multiple columns from multiple tables

DeeBali — Wed, 10 Apr 2024 22:14:43 GMT

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!

Re: Multiple Filters OR on multiple columns from multiple tables

lbendlin — Wed, 10 Apr 2024 23:58:28 GMT

use || or IN function to achieve that.

Re: Multiple Filters OR on multiple columns from multiple tables

DeeBali — Thu, 11 Apr 2024 16:07:11 GMT

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.

Re: Multiple Filters OR on multiple columns from multiple tables

lbendlin — Thu, 11 Apr 2024 16:18:37 GMT

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.

Re: Multiple Filters OR on multiple columns from multiple tables

DeeBali — Thu, 11 Apr 2024 16:39:28 GMT

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

Re: Multiple Filters OR on multiple columns from multiple tables

lbendlin — Thu, 11 Apr 2024 16:49:48 GMT

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

Re: Multiple Filters OR on multiple columns from multiple tables

DeeBali — Thu, 11 Apr 2024 20:48:37 GMT

Sir, this is the error I recicved:

Re: Multiple Filters OR on multiple columns from multiple tables

lbendlin — Thu, 11 Apr 2024 20:53:15 GMT

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

Re: Multiple Filters OR on multiple columns from multiple tables

DeeBali — Thu, 11 Apr 2024 21:07:51 GMT

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.

Re: Multiple Filters OR on multiple columns from multiple tables

lbendlin — Thu, 11 Apr 2024 21:39:55 GMT

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-Forum/ba-p/963216

Re: Multiple Filters OR on multiple columns from multiple tables

DeeBali — Tue, 16 Apr 2024 16:46:51 GMT

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.