Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 )
)
@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 )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |