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 All,
Below is my data.
Date | JobName | Step Number | Step Name | Status |
6/16/2021 | CRM data | Step1 | Load Staging Data | Success |
6/16/2021 | CRM data | Step2 | Load WareHouseData | Success |
6/16/2021 | CRM data | Step3 | Load DataMart | Success |
6/16/2021 | CRM data | Step4 | Process Cube | Fail |
6/16/2021 | Sales data | Step1 | Load Staging Data | Success |
6/16/2021 | Sales data | Step2 | Load WareHouseData | Success |
6/16/2021 | Sales data | Step3 | Load DataMart | Success |
6/16/2021 | Sales data | Step4 | Process Cube | Running |
6/16/2021 | Dimension Load | Step1 | Load Staging Data | Success |
6/16/2021 | Dimension Load | Step2 | Load WareHouseData | Success |
6/16/2021 | Dimension Load | Step3 | Load DataMart | Success |
I want to see the Job status only. I'm taking Job Name ans Status in Matrix and it is showing mw the job status as well if job is successfully completed. but the problem is if a job is failed the it is showing me two status for one job. Success and Failed both. where in I only want to see if all step is success then it should show success and it any of step is failed then it should show as failed with one row or if it is running then it should show as running.
Below is the output which I'm getting right now.
JobName | Status |
CRM data | Success |
CRM data | Fail |
Sales data | Success |
Sales data | Running |
Dimension Load | Success |
Where in the expected output is as below.
JobName | Status |
CRM data | Fail |
Sales data | Running |
Dimension Load | Success |
This is because one step is completed and one step is failed and it is showing both result since it is not unique. hence reqesting you to kindly suggest a solution.
Solved! Go to Solution.
@Anonymous
Create the following measure and add this measure and the Job Name in a table or matrix visuals:
Final Status =
var __t = CALCULATETABLE( Table1 , ALLEXCEPT( Table1 , Table1[JobName] )) return
SWITCH(
TRUE(),
CONTAINS(__t,Table1[Status],"Fail") , "Fail",
CONTAINS(__t,Table1[Status],"Running") , "Running",
"Sucess"
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi, @Anonymous
Please check the below picture and the sample pbix file's link down below.
It is for creating a measure.
Status Last step number measure =
VAR currentdate =
MAX ( 'Table'[Date] )
VAR currentjobname =
MAX ( 'Table'[JobName] )
VAR laststepnumber =
CALCULATE (
MAX ( 'Table'[Step Number] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] = currentdate
&& 'Table'[JobName] = currentjobname
)
)
RETURN
IF (
ISFILTERED ( 'Table'[JobName] ),
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] = currentdate
&& 'Table'[JobName] = currentjobname
&& 'Table'[Step Number] = laststepnumber
)
)
)
https://www.dropbox.com/s/o45li1conjvsrjk/shahabh.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Anonymous
Create the following measure and add this measure and the Job Name in a table or matrix visuals:
Final Status =
var __t = CALCULATETABLE( Table1 , ALLEXCEPT( Table1 , Table1[JobName] )) return
SWITCH(
TRUE(),
CONTAINS(__t,Table1[Status],"Fail") , "Fail",
CONTAINS(__t,Table1[Status],"Running") , "Running",
"Sucess"
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |