Forum Discussion
Help clean up this simple DAX calculation
- 4 years ago
A few thoughts and a question.
- In your initial COUNTROWS you'd be better just to directly COUNTROWS of the table returned from the filter.
- You can make your code a lot more readable by using variables and formatting it.
Those combined would give:
PctInRange = VAR tblGroupAInRange = FILTER( Table1, (Table1[Value] < Table1[RangeLow] || Table1[Value] > Table1[RangeHigh]) && Table1[Category] = "A") VAR cntGroupAInRange = COUNTROWS ( tblGroupAInRange ) VAR cntGroupA = CALCULATE( COUNTROWS ( Table1 ), Table1[Category] = "A" ) VAR Result = 1 - DIVIDE ( cntGroupAInRange, cntGroupA) RETURN ResultIn terms of making it more dynamic depending on how you're intending to use the measure you could just get rid of the hard coded category = A filters. Sliceing on category would pass the filter straight into both.
Lastly are the high/low values the same for all of category A? If so moving them out to a category dimension might help further.
Share a little on how you'd like to use the measure and we can take it further.
A few thoughts and a question.
- In your initial COUNTROWS you'd be better just to directly COUNTROWS of the table returned from the filter.
- You can make your code a lot more readable by using variables and formatting it.
Those combined would give:
PctInRange =
VAR tblGroupAInRange =
FILTER(
Table1,
(Table1[Value] < Table1[RangeLow] || Table1[Value] > Table1[RangeHigh])
&& Table1[Category] = "A")
VAR cntGroupAInRange = COUNTROWS ( tblGroupAInRange )
VAR cntGroupA =
CALCULATE(
COUNTROWS ( Table1 ),
Table1[Category] = "A"
)
VAR Result = 1 - DIVIDE ( cntGroupAInRange, cntGroupA)
RETURN Result
In terms of making it more dynamic depending on how you're intending to use the measure you could just get rid of the hard coded category = A filters. Sliceing on category would pass the filter straight into both.
Lastly are the high/low values the same for all of category A? If so moving them out to a category dimension might help further.
Share a little on how you'd like to use the measure and we can take it further.