cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shahabh786
Frequent Visitor

Retrieve only one rows with two status

Hi All,

Below is my data.

DateJobNameStep NumberStep NameStatus
6/16/2021CRM dataStep1Load Staging DataSuccess
6/16/2021CRM dataStep2Load WareHouseDataSuccess
6/16/2021CRM dataStep3Load DataMartSuccess
6/16/2021CRM dataStep4Process CubeFail
6/16/2021Sales dataStep1Load Staging DataSuccess
6/16/2021Sales dataStep2Load WareHouseDataSuccess
6/16/2021Sales dataStep3Load DataMartSuccess
6/16/2021Sales dataStep4Process CubeRunning
6/16/2021Dimension LoadStep1Load Staging DataSuccess
6/16/2021Dimension LoadStep2Load WareHouseDataSuccess
6/16/2021Dimension LoadStep3Load DataMartSuccess


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.

JobNameStatus
CRM dataSuccess
CRM dataFail
Sales dataSuccess
Sales dataRunning
Dimension LoadSuccess

 

Where in the expected output is as below.

JobNameStatus
CRM dataFail
Sales dataRunning
Dimension LoadSuccess



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.

1 ACCEPTED SOLUTION
Fowmy
Super User IV
Super User IV

@Shahabh786 

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
Jihwan_Kim
Community Champion
Community Champion

Hi, @Shahabh786 

Please check the below picture and the sample pbix file's link down below.

It is for creating a measure.

 

Picture1.png

 

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 accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Fowmy
Super User IV
Super User IV

@Shahabh786 

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

Shahabh786
Frequent Visitor

Hi @Fowmy  Thanks a lot. You're a true champion.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.