March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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
Solved! Go to 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 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.
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.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
134 | |
91 | |
89 | |
64 | |
58 |
User | Count |
---|---|
201 | |
137 | |
107 | |
72 | |
68 |