dax issue on months

[animebuff]

left side visual is what by default we get in PBI
what I need is left side visual without jan month because there is no data for jan month in fact table

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 

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

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

[Ray_Minds]

Hi @animebuff 

As I go through the measures provided by you.  You mention that it doesn’t work properly. Here is update and tested version of that measure you can use to solve your issue as you can see the Fig below.

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

[animebuff]

nov month drill down values will not match,

it will show blank for values that needs to show 1

[danextian]

Hi @animebuff 

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

This is telling DAX to return 1 when both the numerator and the denominator are both blank and this is true for January.

 numerator=BLANK(),0,

This is telling DAX to return 0 a when the numerator is blank  and this still is true for January. 0 value categories are not hidden so a column for January will still appear.

So why not just use

DIVIDE ( numerator, denominator )

Note: I am using DIVIDE as a best practice.

[animebuff]

but, in my scenario divide dax will not change the results

and jan month is not present in fact table so I need to avoid it 

[danextian]

what do you mean by ' in my scenario divide dax will not change the results"?

I tested this simple DIVIDE formula and it excludes january

[animebuff]

@danextian thanks for the reply,

but your logic will give correct results on month wise but if you drill down to region it will fail
bottom 2 ( 3 and 4 ) are based on the logic you gave

my expectation is I need to get 3rd (image) on month wise and 2rd (image) on region wise drill down
note: drill down done on nov month

[danextian]

You can use ISINSCOPE to check whether a column is the level in a hierarchy of levels.

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 )

[pallavi_r]

Hi @animebuff ,

Is it possible for you to share your sample pbix file on the above. It would be easier to check the issue and share the fix.

Thanks,

Pallavi

[animebuff]

is there a way to attach pbix file?

[pallavi_r]

Hi @animebuff ,

Sorry,  google drive or dropbox link will do. Thanks, Pallavi

[animebuff]

okay, will try to do it

[pallavi_r]

Thank you