Forum Discussion

lathaaa's avatar
lathaaa
Frequent Visitor
4 years ago
Solved

IF condition based on two columns

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.

 

Need Help
  • lathaaa's avatar
    lathaaa
    4 years ago

    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 🙂

6 Replies

  • AntonioM's avatar
    AntonioM
    Solution Sage
    4 years ago

    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

     

  • lathaaa's avatar
    lathaaa
    Frequent Visitor
    4 years ago

    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's avatar
      AntonioM
      Solution Sage
      4 years ago

      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's avatar
        lathaaa
        Frequent Visitor
        4 years ago

        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 🙂