Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX Calculate SUM of Conditional multiple columns

I am looking for DAX for the below condition where get the SUM of Count for each individual TestName and Success column for that individual date.

 

PassCount =
If [TestName] = "XMods And More", [Success] = "TRUE" then get SUM of Count
elseIf [TestName] = "IQA", [Success] = "TRUE" then get SUM of count
etc..

 

 

 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    I was thinking in terms of a calculated column.

     

    Since you're doing this as a measure, I think it can be simpler:

    CALCULATE ( SUM ( Table1[Count] ), Table1[Success] = "TRUE" )

10 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    How about this?

    PassCount =
CALCULATE (
    SUM ( Table1[Count] ),
    ALLEXCEPT ( Table1, Table1[TestName], Table1[Timestamp] ),
    Table1[Success] = "TRUE"
)

     

    The logic is pretty much the same as this:

    PassCount =
SUMX (
    FILTER (
        ALL ( Table1 ),
        Table1[Success] = "TRUE"
            && Table1[TestName]  = EARLIER ( Table1[TestName]  )
            && Table1[Timestamp] = EARLIER ( Table1[Timestamp] )
    ),
    Table1[Count]
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Ok, I tried! but somehow the calculation is messing with me. I need to get the percentage and realized I have one category for each day so skipped the Timestamp.

       

      FailureCount = CALCULATE ( SUM ( AvailabilityResults[Count] ),ALLEXCEPT ( AvailabilityResults, AvailabilityResults[TestName]), AvailabilityResults[Success] ="FALSE")
       
      PassCount= CALCULATE ( SUM ( AvailabilityResults[Count] ),ALLEXCEPT ( AvailabilityResults, AvailabilityResults[TestName]), AvailabilityResults[Success] ="TRUE")
       
      Percentage= 
      DIVIDE([Passcount], ([Passcount] + [FailureCount]))
       
      Ex: TestName = IQA, Coverage,  I have only PassCount = 85 for today and I am expecting 100% but it is 99.97%/99.86%

       

      • AlexisOlson's avatar
        AlexisOlson
        Icon for Super User rankSuper User
        4 years ago

        I certainly wouldn't expect it to work if you remove [TimeStamp] from the measure.