topic Re: Count Distinct where JobStatus = Max(JobStatus) in DAX Commands and Tips

Count Distinct where JobStatus = Max(JobStatus)

kkt — Fri, 04 Sep 2020 11:16:08 GMT

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

Re: Count Distinct where JobStatus = Max(JobStatus)

amitchandak — Fri, 04 Sep 2020 12:10:43 GMT

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

Re: Count Distinct where JobStatus = Max(JobStatus)

Anonymous — Fri, 04 Sep 2020 12:19:27 GMT

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

Re: Count Distinct where JobStatus = Max(JobStatus)

Greg_Deckler — Fri, 04 Sep 2020 12:20:25 GMT

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

Re: Count Distinct where JobStatus = Max(JobStatus)

dedelman_clng — Fri, 04 Sep 2020 12:55:52 GMT

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

Re: Count Distinct where JobStatus = Max(JobStatus)

kkt — Fri, 04 Sep 2020 12:32:45 GMT

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.

Re: Count Distinct where JobStatus = Max(JobStatus)

Anonymous — Fri, 04 Sep 2020 12:53:37 GMT

One more time...