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