Forum Discussion

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

dax logic not working on 2 levels

 

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 
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
2

dec

3jan
data protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • danextian's avatar
    danextian
    1 year ago

    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 )

     

     

     

6 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      animebuff
      Icon for Helper I rankHelper I
      1 year ago

      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's avatar
        danextian
        Icon for Super User rankSuper User
        1 year ago

        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's avatar
    anmolmalviya05
    Icon for Super User rankSuper User
    1 year ago

    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
    )
    )