Forum Discussion
DAX issue
Hi wini_R
Please try this measure:
MEASURE = VAR _currentRegion = SELECTEDVALUE ( 's rate'[region] ) VAR _ifdenominator = COUNTROWS ( SUMMARIZE ( FILTER ( ALLSELECTED ( 's rate' ), 's rate'[region] = _currentRegion && 's rate'[inclusion flag] = "n" ), 's rate'[mk], 's rate'[region] ) ) VAR _outcome = CALCULATE ( COUNT ( 'S rate'[success] ), 'S rate'[inclusion flag] = "Y" ) / COUNT ( 'S rate'[success] ) RETURN IF ( _outcome <> BLANK (), _outcome, IF ( _ifdenominator <> 1, 1, 0 ) )The result is as follow:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi animebuff,
I believe the following measure should give you what you need:
ratio =
VAR _numerator = CALCULATE(COUNT('S rate'[success]),'S rate'[inclusion flag]="Y")
VAR _denominator = COUNT('S rate'[success])
VAR _result =
SWITCH(
TRUE(),
ISBLANK(_denominator), 1,
ISBLANK(_numerator), 0,
DIVIDE(_numerator, _denominator)
)
RETURN _result
That's the outcome:
If I'm not missing anything, it appears there are denominators for Africa, Aus, NA and UAE that's why we get zeros in the chart (at least this part of your formula referring to denominator returns the result: COUNT('S rate'[success]) )
if I try ISBLANK(num) && ISBLANK(den),1 as one condition of switch statement which is my actual ask
then I get 1 for the months that doesn't even have data, for example if my month columns has extra month like jan of 25, even if there is no data, i still will get 1 by logic
that will be wrong right?
this logic works when under my region drill down but for month it will show wrong values