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
maneschr2022
Helper II
Helper II

How to create dax to count total by filtering/excluding a specific value from it

Hi I need to create a card that counts the number of cases  by its phase.

 

So for each Case Stream there is a Phase ID value related to the Phase, but one case stream has a different value (inverted meaning) in comparison of other streams, example:

 

Phases       Phase ID       Case Stream 

 Open         22                    Family

 Open          3                     Administrative

 Open         22                     Commercial

Open            5                     Other

In progress     3                   Family

In progress      22             Administrative

In progress       3               Commercial

In progress        6                 Other

 

As you can see Administrative Case Stream has is special as its value means a different phase, so for example if  I need a dax to count total number of cases that are "Open" I would like to exclude Phase ID 22 just for "Administrative"  as for this case means In progress (not open).

Can someone help me how would be the dax based on this example?

Thank you

2 REPLIES 2
amitchandak
Super User
Super User

@maneschr2022 ,

 

calculate(Count(Table[Phase ID]), filter(Table, Table[Phase ID]<> 22 && Table[Phase] = "Open"))

 

or

 

calculate(Count(Table[Phase ID]), filter(allselected(Table), Table[Phase ID]<> 22 && Table[Phase] = "Open"))

 

or

 

calculate(Count(Table[Phase ID]), filter(all(Table), Table[Phase ID]<> 22 && Table[Phase] = "Open"))

Hi @amitchandak 

 

Thanks for your reply, I think I didn't explain it well, but what I need to get as a result is a count of the total open cases but filtering out the combination of the values "Administrative" and "22" to count properly the cases that are open. 

Im trying to find if there is a way to filtering JUST the combination of those two values? because I need to consider the 22 Phase ID for Family and Commercial Case Stream to get the total number of cases that are open.

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.

Top Solution Authors