Count Distinct where JobStatus = Max(JobStatus)

[kkt]

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	01-Sep-2020 09:00	01-Success
Hong Kong	StreamA	Job2	01-Sep-2020 10:00	03-Running
Hong Kong	StreamA	Job3		02-Pending
Indonesia	StreamA	Job1		02-Pending
Indonesia	StreamA	Job2		02-Pending
Indonesia	StreamA	Job3		02-Pending
Japan	StreamA	Job1	01-Sep-2020 09:00	01-Success
Japan	StreamA	Job2	01-Sep-2020 10:00	01-Success
Japan	StreamA	Job3	01-Sep-2020 11:00	01-Success
Singapore	SteamA	Job1	01-Spe-2020 09:00	01-Success
Singapore	StreamA	Job2	01-Sep-2020 10:00	04-Failed
Singapore	StreamA	Job3		02-Pending

I need to

Step 1 : Determine the Overall Status by JobStream :

select top 1 Country, JobStream, status from Table A group by Country, JobStream order by Status desc 

Country	Job Stream	OverallStatus
Hong Kong	StreamA	03-Running
Indonesia	StreamA	02-Pending
Japan	StreamA	01-Success
Singapore	StreamA	04-Failed

Step 2 :

And then I have 4 Measures to Count the #of Countrys by Overall Status, so in Power BI I have the following measures

Pending=Calculate(DistinctCount(Country), OverallStatus="02-Pending")

Success=Calculate(DistinctCount(Country), OverallStatus="01-Success")

Failed=Calculate(DistinctCount(Country), OverallStatus="04-Failed")

Running=Calculate(DistinctCount(Country), OverallStatus="03-Running")

So how do I do step 1 & 2 ? Can I combine step 1 and step 2 by using different DAX function  ? 

Please help. Thanks.

Kitty

[dedelman_clng]

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

[Greg_Deckler]

@kkt - This looks like Lookup Min/Max - https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

So:

Overall Status Measure =
  VAR __Max = MAX('TableA'[Job Start Time])
RETURN
  MAXX(FILTER('TableA',[Job Start Time] = __Max),[Status])

[Anonymous]

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

[amitchandak]

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

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

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