measure using If with multiple condition

[jitpbi]

Hi,

The following is the sample dataset of my actual dataset:

Device	Status	Datetime
A	Good	10/23/2020 8:10:00 AM
A	Faulty	10/23/2020 4:15:00 PM
A	Good	10/24/2020 11:10:00 AM
B	Good	10/23/2020 8:10:00 AM
B	Good	10/23/2020 4:15:00 PM
B	Faulty	10/24/2020 11:10:00 AM
C	Faulty	10/23/2020 8:10:00 AM
C	Good	10/23/2020 4:15:00 PM
C	Faulty	10/24/2020 11:10:00 AM

I have to display those device's names which latest status is faulty.

I tried unsuccessfuly the below dax to create a measure:

faulty_device = if('Table'[Status]=" Faulty" && Table[DateTime]= max(Equipment[DateTime]), CONCATENATEX(Table,Table[Device], "is faulty device","No Device is faulty Currently"))

Please suggest how to achieve this.

Thanks

[v-shex-msft]

Hi @jitpbi,

You can try to use the following measure formula if it works on your side:

faulty_device =
VAR currSatatus =
    SELECTEDVALUE ( 'Table'[Status] )
VAR _lastDate =
    CALCULATE (
        MAX ( Equipment[DateTime] ),
        ALLSELECTED ( Table ),
        VALUES ( 'Table'[Device] )
    )
RETURN
    IF (
        currSatatus = " Faulty"
            && Table[DateTime] = _lastDate,
        CONCATENATEX ( VALUES ( Table[Device] ), [Device], "is faulty device" ),
        "No Device is faulty Currently"
    )

If this not help, please share some dummy data to test coding formula.

Regards,

Xiaoxin Sheng

[jitpbi]

Hi @v-shex-msft ,

When trying to use the measure formula you shared, it is not accepting Table[DateTime] in the formula.

faulty_device =

VAR currSatatus =

SELECTEDVALUE ( 'Table'[Status] )

VAR _lastDate =

CALCULATE (

MAX ( Equipment[DateTime] ),

ALLSELECTED ( Table ),

VALUES ( 'Table'[Device] )

)

RETURN

IF (

currSatatus = " Faulty"

&& Table[DateTime] = _lastDate,

CONCATENATEX ( VALUES ( Table[Device] ), [Device], "is faulty device" ),

"No Device is faulty Currently"

)

Thanks

[v-shex-msft · Accepted solution]

HI @jitpbi,

It seems like I forget to pack it with aggregate functions, please try this one if it works:

faulty_device =
VAR currSatatus =
    SELECTEDVALUE ( 'Table'[Status] )
VAR _lastDate =
    CALCULATE (
        MAX ( Equipment[DateTime] ),
        ALLSELECTED ( Table ),
        VALUES ( 'Table'[Device] )
    )
RETURN
    IF (
        currSatatus = " Faulty"
            && MAX( Table[DateTime] ) = _lastDate,
        CONCATENATEX ( VALUES ( Table[Device] ), [Device], "is faulty device" ),
        "No Device is faulty Currently"
    )

Regards,

Xiaoxin Sheng

[CNENFRNL]

@jitpbi , what the relationship with Table and Equipment (cardinality and direction)? As you know, relationship plays a critical role when authoring measures.

For the moment, I came up with a measure based on the single table like this,

Faulty Devices = 
VAR __tb =
    FILTER (
        FILTER (
            'Table',
            'Table'[Datetime]
                = CALCULATE ( MAX ( 'Table'[Datetime] ), ALLEXCEPT ( 'Table', 'Table'[Device] ) )
        ),
        'Table'[Status] = "Faulty"
    )
RETURN
    CONCATENATEX ( __tb, 'Table'[Device], UNICHAR ( 10 ) )

When dragged into a card viz, it returns

[Fowmy]

@jitpbi 

Add the following measure;

Faulty Devices = 
VAR __STATUS =  LASTNONBLANKVALUE(Table5[Datetime],MAX(Table5[Status]))  RETURN
IF( __STATUS = "Faulty", __STATUS , BLANK() )

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn