Forum Discussion

animebuff's avatar
animebuff
Icon for Helper I rankHelper I
1 year ago

dax relationship issue in drilldown

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:

 

default dax = CALCULATE(COUNT('S rate'[success]),'S rate'[inclusion flag]="Y")/COUNT('S rate'[success])
 
modified dax = CALCULATE(CALCULATE(COUNT('S rate'[success]),'S rate'[inclusion flag]="Y")/COUNT('S rate'[success]),CROSSFILTER(region[region],'S rate'[region],None))
 
-----------------------------------------------------------------------------------------------------------------------
 
tables:
 
s rate 
mkregioninclusion flagsuccess
1apacy95
1europen85
1lay91
1nan97
1african88
1australian95
1apacy93
2europey94
2lan89
2nan88
2african99
2australian80
2uaen90

 

region

region
apac
europe
la
na
africa
australia
uae

 

month

mkm
1nov
2dec
data protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi animebuff ,

     

    Please try the following DAX formula, the RELATEDTABLE function can be used to ensure that the measure is calculated correctly in the region table:

    New dax = 
VAR _count_flag=IF(CALCULATE(COUNTROWS(RELATEDTABLE('s rate')),'s rate'[inclusion flag]="y")<>0,CALCULATE(COUNTROWS(RELATEDTABLE('s rate')),'s rate'[inclusion flag]="y"),0)
VAR _y_region_count = COUNTROWS(RELATEDTABLE('s rate'))
RETURN 
DIVIDE(_count_flag,_y_region_count)

     

    Result:

     

    Best Regards,
    Zhu

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

    • animebuff's avatar
      animebuff
      Icon for Helper I rankHelper I
      1 year ago

      Anonymous thanks for the reply,

       

      still it didn't met the requirement, I gave count as example but I need is distinct count and the other issue is africa, australia and na should show 1 since both numerator and denominator is not available for these countries and only uae should show 0 since numerator is 0 for uae