dax relationship issue in drilldown

[animebuff]

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 

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

[v-linhuizh-msft]

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]

@v-linhuizh-msft 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