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

[maneschr2022]

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

[amitchandak]

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

[maneschr2022]

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.