Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
acerNZ
Helper III
Helper III

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

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

1 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:

v-robertq-msft_0-1618887902917.png

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:

v-robertq-msft_1-1618887902920.png

 

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.

View solution in original post

3 REPLIES 3
acerNZ
Helper III
Helper III

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

2021-04-19 23_07_26-sampledataworkorders - Excel.png

 

 

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:

v-robertq-msft_0-1618887902917.png

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:

v-robertq-msft_1-1618887902920.png

 

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
Super User
Super User

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.