dax logic not working on 2 levels

[animebuff]

my fact table has 2 months ( nov, dec) data & month table has 3 months data ( nov, dec, jan)

since fact is only having 2 months data, the metric is expected to only show 2 months data

in my case, it shows 3 months data 

the logic I have written is if numerator and denominator is blank then 1, if numerator alone blank then 0 if numerator & denominator are not blank then numerator/denominator

month and region drill down is showing the expected value the only issue is I get an extra jan month data 

the image show drilldown of nov month

I need to get 3rd image on month wise & 2rd image on Nov drilldown 

whatever I try I either get 1 2 combination or 3 4 combination not 3 2 combination 

-----------------------------------------------------------------------------------------------------------------------

dax:

Measure =

var numerator = CALCULATE(DISTINCTCOUNT('S rate'[success]),'S rate'[inclusion flag]="Y")

var denominator = DISTINCTCOUNT('S rate'[success])

return

SWITCH(

    TRUE(),

    numerator=BLANK() && denominator=BLANK(),1,

    numerator=BLANK(),0,

    numerator/denominator

)

-----------------------------------------------------------------------------------------------------------------------

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
3	jan

[Ashish_Mathur]

Hi,

PBI file attached.

[danextian · Accepted solution]

I replied to your original post.

Measure02 = 
VAR numerator =
    CALCULATE (
        DISTINCTCOUNT ( 'S rate'[success] ),
        'S rate'[inclusion flag] = "Y"
    )
VAR denominator =
    DISTINCTCOUNT ( 'S rate'[success] )
VAR _pct =
    DIVIDE ( numerator, denominator )
RETURN
    IF ( ISINSCOPE ( region[region] ), _pct + 0, _pct )

[animebuff]

@danextian thanks for the reply

everything is perfect 👌 but
numerator = blank && denominator = blank,1
numerator alone is blank then 0

this part didn't work, for both cases it shows 0

for nov month uae also has 1 because for nov uae has no numerator or denominator 

[danextian]

I'm confused. Wasnt your goal to not show a month when it had no data but still show all regions when drilled down?

[anmolmalviya05]

Hi @animebuff, Please try below measure:

Measure =
VAR numerator = CALCULATE(
DISTINCTCOUNT('S rate'[success]),
'S rate'[inclusion flag] = "Y"
)
VAR denominator = DISTINCTCOUNT('S rate'[success])
RETURN
IF(
numerator = BLANK() && denominator = BLANK(),
BLANK(),
SWITCH(
TRUE(),
numerator = BLANK(), 0,
numerator / denominator
)
)

[animebuff]

@anmolmalviya05 thanks for the reply

numerator = BLANK() && denominator = BLANK() needs to be 1 not blank