Forum Discussion
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 | 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
6 Replies
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
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.
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 ) )
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 ) )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])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
