Forum Discussion

s_chayaphon's avatar
s_chayaphon
New Member
3 years ago
Solved

count with over calculation

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's avatar
    tamerj1
    3 years ago

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

1 Reply

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

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