Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Retrieve only one rows with two status

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...
DAX
power bi
  • Fowmy's avatar
    Fowmy
    5 years ago

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

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

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