IF condition based on two columns

[lathaaa]

Hi All, need help on writing DAX

the logic should be if that particular ID is only Failed - 0, only Success - 1, Success & failed - 2, Success & Failed & reprocess - 3

the expected column should be like below image.

[lathaaa]

@AntonioM , I tried with the code which you provided, expected column  is giving the result based on only status, but the condition is also to check based on ID & Status

Ex- if ID is 456 and failed, success & reprocess then 3

[AntonioM]

@lathaaa Could you show me what you're getting when you've tried that? The line

var statuses = CALCULATETABLE( VALUES('Table'[Status]), ALL('Table'[Status]) )

should keep the filter on ID and check for all statuses. Do you have any other other columns in the table?

[lathaaa · Accepted solution]

@AntonioM , got the solution by using this DAX code

var statuses = CALCULATETABLE(VALUES(T_Invoice[Check]),T_Invoice[Report_ID] = EARLIER(T_Invoice[Report_ID]), ALL(T_Invoice) )

Thank you 🙂

[lathaaa]

@AntonioM  Yes, the table has other columns
the code i have tried - 
Column_check =

var statuses = CALCULATETABLE( VALUES(T_Invoice[Check]), ALL(T_Invoice[Check]) )

return

SWITCH(

TRUE(),

CONTAINS(statuses,T_Invoice[Check], "True") && CONTAINS(statuses,T_Invoice[Check], "False") && CONTAINS(statuses,T_Invoice[Check], "blank"),

3,

CONTAINS(statuses,T_Invoice[Check], "True") && CONTAINS(statuses,T_Invoice[Check], "blank"),

2,

CONTAINS(statuses,T_Invoice[Check], "True"),

1,

CONTAINS(statuses,T_Invoice[Check], "blank"),

0

)

when i try to filter on one ID and check for status, column check is the result but expected result should be as it is "True" & "blank" for that ID , it has to show 2 instead of 0 & 1

thank you

[lathaaa]

@amitchandak @Greg_Deckler , could you please help me on this DAX

[AntonioM]

Please give this a try

Column = 
var statuses = CALCULATETABLE( VALUES('Table'[Status]), ALL('Table'[Status]) )
return
SWITCH(
    TRUE(),
    CONTAINS(statuses,'Table'[Status], "reprocess") && CONTAINS(statuses,'Table'[Status], "Failed") && CONTAINS(statuses,'Table'[Status], "Success"),
    3,
    CONTAINS(statuses,'Table'[Status], "Failed") && CONTAINS(statuses,'Table'[Status], "Success"),
    2,
    CONTAINS(statuses,'Table'[Status], "Success"),
    1,
    CONTAINS(statuses,'Table'[Status], "Failed"),
    0
)

Which will give you