Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
kkt
Frequent Visitor

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: 

CountryJob StreamJobsJob Start TimeStatus 
Hong KongStreamAJob101-Sep-2020 09:0001-Success
Hong KongStreamAJob201-Sep-2020 10:0003-Running
Hong KongStreamAJob3 02-Pending
IndonesiaStreamAJob1 02-Pending
IndonesiaStreamAJob2 02-Pending
IndonesiaStreamAJob3 02-Pending
JapanStreamAJob101-Sep-2020 09:0001-Success
JapanStreamAJob201-Sep-2020 10:0001-Success
JapanStreamAJob301-Sep-2020 11:0001-Success
SingaporeSteamAJob101-Spe-2020 09:0001-Success
SingaporeStreamAJob201-Sep-2020 10:0004-Failed
SingaporeStreamAJob3 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 

 

CountryJob StreamOverallStatus
Hong KongStreamA03-Running
IndonesiaStreamA02-Pending
JapanStreamA01-Success
SingaporeStreamA04-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

6 REPLIES 6
dedelman_clng
Community Champion
Community Champion

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

 

 

2020-09-04 07_55_19-scratch3 - Power BI Desktop.png

 

Hope this helps

David

Greg_Deckler
Community Champion
Community Champion

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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@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
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.