Forum Discussion

kkt's avatar
kkt
Frequent Visitor
5 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 ...
amitchandak's avatar
amitchandak
Super User
5 years ago

kkt , Not very clear. You create a measure in step 2. and put them in visual with Country, Job Stream

or Country, Job Stream, OverallStatus

kkt's avatar
kkt
Frequent Visitor
5 years ago

amitchandak, In the report, we will only show the 4 measures to show how many countries have Pending, Success, Failed, Pending jobs. We will separate the Job Stream by different report tabs. 

Hope that clarifies your query. 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    One more time...

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