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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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"))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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