topic Re: Retrieve only one rows with two status in DAX Commands and Tips

Retrieve only one rows with two status

Anonymous — Wed, 16 Jun 2021 07:45:21 GMT

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.

Re: Retrieve only one rows with two status

Fowmy — Wed, 16 Jun 2021 08:02:39 GMT

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

Re: Retrieve only one rows with two status

Anonymous — Wed, 16 Jun 2021 08:12:49 GMT

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

Re: Retrieve only one rows with two status

Jihwan_Kim — Wed, 16 Jun 2021 08:13:23 GMT

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