Forum Discussion
DAX issue
requirement:
left side visual is what by default we get in PBI
what I need is right side visual but the values of all region should not be 0.31 rather
apac - 1
europe - 0.5
la - 0.5
afracia - 1
australia - 1
na - 1
uae - 0
only apac, europe and la have both numerator and denominator so it should give respective values
uae only has denominator so it is 0 (0/anything)
rest regions doesn't have data so it should give value as 1
-----------------------------------------------------------------------------------------------------------------------
dax:
| mk | region | inclusion flag | success |
| 1 | apac | y | 95 |
| 1 | europe | n | 85 |
| 1 | la | y | 91 |
| 1 | na | n | 97 |
| 1 | africa | n | 88 |
| 1 | australia | n | 95 |
| 1 | apac | y | 93 |
| 2 | europe | y | 94 |
| 2 | la | n | 89 |
| 2 | na | n | 88 |
| 2 | africa | n | 99 |
| 2 | australia | n | 80 |
| 2 | uae | n | 90 |
region
| region |
| apac |
| europe |
| la |
| na |
| africa |
| australia |
| uae |
month
| mk | m |
| 1 | nov |
| 2 | dec |
- Anonymous1 year ago
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.
9 Replies
- animebuffHelper I
as I stated above, x-axis has 2 fields, 1st month and then 2rd region
my default dax is CALCULATE(COUNT('S rate'[success]),'S rate'[inclusion flag]="Y")/COUNT('S rate'[success])
so by default, the data will divide by month on 1st level & divide by region of 2nd level
and now my business have a requirement above this logic,
they want to see all regions available for every month
for. example, tho for nov month has data only for region apac, australia and la
they need to see other region as well with if numerator and denominator is not available (or blank) then it should show 100% or 1 and if numerator not available it should show 0 ( 0 by anything is 0)
this is the result I need to get
uae - 0
apac - 1
europe - 0.5
la - 0.5
afracia - 1
australia - 1
na - 1
-----------------------------------------------------------------------------------------------------------------------
modified dax = CALCULATE(CALCULATE(COUNT('S rate'[success]),'S rate'[inclusion flag]="Y")/COUNT('S rate'[success]),CROSSFILTER(region[region],'S rate'[region],None))
this is the logic I wrote to get every region under a month, I got the regions with this logic but for every region I get same values as that month
- wini_RSolution Supplier
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 _resultThat'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]) )
- animebuffHelper I
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
- AnonymousNot applicable
Hi animebuff
Here are 2 workarounds:
1:
Use the default dax:
Measure = CALCULATE ( COUNT ( 'S rate'[success] ), 'S rate'[inclusion flag] = "Y" ) / COUNT ( 'S rate'[success] )Then right-click the [region] field in the X-axis and select Show items with no data:
The result is as follow:
2:
Change the measure as the follow:
Measure 2 = VAR _AAA = CALCULATE ( COUNT ( 'S rate'[success] ), 'S rate'[inclusion flag] = "Y" ) / COUNT ( 'S rate'[success] ) RETURN IF(_AAA<>0,_AAA,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.- animebuffHelper I
both methods will show data as 0 for months which doesn't have date surrogate key in fact table
- AnonymousNot applicable
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.