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 ...
dedelman_clng's avatar
dedelman_clng
Community Champion
6 years ago

Hi kkt -

 

Based on your description, you can do these two measures

 

 

Current Status =
CALCULATE (
    MAX ( TableA[Status ] ),
    ALLEXCEPT ( TableA, TableA[Country], TableA[Job Stream] )
)

CountryCount = 
VAR __myStatus =
    SELECTEDVALUE ( TableA[Status] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( TableA[Country] ),
        FILTER ( TableA, [Current Status] = __myStatus )
    )

 

 

 

Hope this helps

David