How to create a measure that excludes certain values

[anon23131213123]

I am trying to create a measure that will count items that only have a single status. For example in the table below I am looking to only count InProgress items, Base2 would be excluded as it was InProgress but then tranisitioned to Aborted.  Using CALCULATE(COUNTROWS will give an answer of 3 as it will count Base1,Base2,Base3 as having a status of InProgress

Item	State
Base1	InProgress
Base2	InProgress
Base2	Aborted
Base3	InProgress

[tackytechtom · Accepted solution]

Hi @anon23131213123 ,

How about this:

Here the DAX:

MeasureCountStatus = 
VAR _helpTable =
SUMMARIZE (
    Table,
    Table[Item],
    "countNotInProgress", CALCULATE ( DISTINCTCOUNT ( Table[State] ), Table[State] <> "InProgress" ),
    "countInProgress", CALCULATE ( DISTINCTCOUNT ( Table[State] ), Table[State] = "InProgress" )
)
RETURN
CALCULATE ( COUNTROWS ( FILTER ( _helpTable, [countNotInProgress] = 0 && [countInProgress] = 1 ) ) ) 

Let me know if this works for you 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[anon23131213123]

That worked thanks!