Count of (Filter of Table 1 Col1 with 3 (and /OR) Options AND Table col 2 with multiple options...

[acerNZ]

Hi Experts

I have originally posted here: Re: Filter Multiple Columns for multiple filter options to create a Measure and thankful to @amitchandak to respond. But Power BI forum played up and was showing, message not submitted and it made N copies for each for my submit( Unknown to me). Obviously, my response to @amitchandak might not be noticed. 

So posting again.

I am looking for the following function in DAX / Power Query as long as I get this done

Count of (Filter of Table 1 Col1 with 3 (and /OR) Options AND Table col 2 with multiple options...

1. Count of Table1 Col1 with 3 filters gives me the value 156 

2. Count of Table1  Col2 with 2 filters gives me the value 179

3. Count of table1 col 3 with 3 filters gives me, the value 185 

each independently.  can I combine all the above in one formula either in Desktop or Power Query as (AND) all the above filters is least of 3, which is 156. 

Any help is appreciated. Thank you

[acerNZ]

@MFelix  Thank you. I was thinking hard on how to attach it, one is via the GITHUB but I think this is easy 

https://www.contextures.com/tablesamples/sampledataworkorders.zip 

The count of column "District" (Filter of North) &&  Count of column "Service" (filter of "Replace" OR Access" OR "Deliver") && Count of column "Techs" && count of column "Payment" ( with filter of "Account" OR "C.O.D" OR "Credit") 

Reason: The Measure I derive is used other measures to derive KPI. I really appreciate your help.

Thanks a ton

Any ideas or advice. As long as I get this done DAX or M language, I am fine. 

[v-robertq-msft · Accepted solution]

Hi, @acerNZ 

I jumped over from your case link in the original case, and I think you can try this measure according to your real data:

This is my test data based on your picture:

Min of count =

var _district=COUNTX(FILTER(ALL('Table'),[District]="North"),[District])

var _service=COUNTX(FILTER(ALL('Table'),[Service] in {"Replace","Access","Deliver"}),[Service])

var _payment=COUNTX(FILTER(ALL('Table'),[Payment] in {"Account","COD","Credit"}),[Payment])

return

IF(_district<=_service,IF(_district<=_payment,_district,_payment),IF(_service<=_payment,_service,_payment))

Then create a card chart to place it:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @acerNZ ,

Has refered by @amitchandak on your previous post this can be done using OR and IN syntax. Can you please share some sample data and expected resutl?