count with over calculation

[s_chayaphon]

Hi,

I've data as below sample, and I want to count on how many App Delay, Failed and Ontime.

As you can see one app have many batch if one batch failed then the app define as failed if there is no fail but delay then the app deifne as delay and if no fail and delay then the app define as ontime

I'd like to create measure from below table to show in Card KPI for Number of App Ontime, Delay and Failed

by below sample data it should show :
App Otime = 1
App Delay = 1
App Failed = 2

Total App = 4

Could you pelase advise how to write DAX for create meausre for App Otime, App Delay and App Failed using in Card KPI.

Thanks

[tamerj1 · Accepted solution]

Hi @s_chayaphon 

Manually create FilterTable that contains the three App Status values:

Otime
Delay 
Failed 

then count measure would be

Count =
SUMX (
CROSSJOIN ( VALUES ( FilterTable[App Status] ), VALUES ( 'Table'[App] ) ),
INT (
FilterTable[App Status]
IN SELECTCOLUMNS (
TOPN ( 1, CALCULATETABLE ( 'Table' ), 'Table'[Status Code] ),
"@Status", 'Table'[Status]
)
)
)