Forum Discussion

kkt's avatar
kkt
Frequent Visitor
6 years ago

Count Distinct where JobStatus = Max(JobStatus)

Hi,    I have the following table A which stores the Job Stream and Job Status of some jobs by Country. TableA:  Country Job Stream Jobs Job Start Time Status  Hong Kong StreamA Job1 ...
Anonymous's avatar
Anonymous
Not applicable
6 years ago
Success = 
// just change the status to the one you want
// and rename the measure accordingly to get
// all the 4 measures you're after
var __status = "01-Success"
return
CALCULATE(
    DISTINCTCOUNT( 'Table'[Country] ),
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                'Table',
                'Table'[Country],
                'Table'[Stream]
            ),
            "@LatestStatus",
                CALCULATE( MAX( 'Table'[Status ] ) )
        ),
        [@LatestStatus] = __status
    ),
    ALL( T )
)