Forum Discussion
SUM for Filtered Values
I'm having a problem with something that seems really simple but I've wasted nearly a day trying to get it together. I suspect my TSQL background is interfering with may DAX thinking. I have two ...
To whom it may concern:
Here's the solution that worked:
I created two measures...a hidden one:_adjustments = IF(AND(HASONEVALUE(ARDoc[ARDocKey]), SUM(ARDoc[Initial Balance]) <> 0),CALCULATE(SUM('ARAdjustments'[Adjustment Amount]), USERELATIONSHIP(ARAdjustments[Date Applied], 'Date'[Date]))
// I believe the different USERELATIONSHIP() parms fixed the slicer issue.
// The HASONEVALUE() keeps it from returning a total row and the SUM(ARDoc[Initial Balance]) <> 0 causes some invalid data from messing up the rows.// Also note that rows that do not meet these criteria return <BLANK>, NOT 0 (zero).
...and the measure I ultimately put in the table visual:Adjustments = SUMX(VALUES(ARDoc[ARDocKey]), [_adjustments])This corrected the slicer behavior and gives my report the correct column total. :)
Ok - changed from a calculated column to a measure. Doesn't work. Appears ok when the date slicer is set to include all the adjustments. But when I set the date slicer prior to the application date and after the document dates - the measure still seems to suml the adjustments, when they should be filtered out.
To whom it may concern:
Here's the solution that worked:
I created two measures...a hidden one:
_adjustments = IF(AND(HASONEVALUE(ARDoc[ARDocKey]), SUM(ARDoc[Initial Balance]) <> 0),
CALCULATE(SUM('ARAdjustments'[Adjustment Amount]), USERELATIONSHIP(ARAdjustments[Date Applied], 'Date'[Date]))
// I believe the different USERELATIONSHIP() parms fixed the slicer issue.
// The HASONEVALUE() keeps it from returning a total row and the SUM(ARDoc[Initial Balance]) <> 0 causes some invalid data from messing up the rows.
// I believe the different USERELATIONSHIP() parms fixed the slicer issue.
// The HASONEVALUE() keeps it from returning a total row and the SUM(ARDoc[Initial Balance]) <> 0 causes some invalid data from messing up the rows.
// Also note that rows that do not meet these criteria return <BLANK>, NOT 0 (zero).
...and the measure I ultimately put in the table visual:
...and the measure I ultimately put in the table visual:
Adjustments = SUMX(VALUES(ARDoc[ARDocKey]), [_adjustments])
This corrected the slicer behavior and gives my report the correct column total. :)