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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.