Forum Discussion
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", ...
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" )
AlexisOlson
Super User
Super UserHow 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]
)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
I certainly wouldn't expect it to work if you remove [TimeStamp] from the measure.
okay, I added Timestamp, and it' same. I see that it's counting all FALSE values regardless of the TestName category.
I need to count for each TestName and Success,
- AlexisOlson
Can you share some example data in a format that can be copied and pasted from? Please include the result you expect to match as well.