Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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
@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])
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, 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 )
)
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |