Forum Discussion

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

dax issue on months

 

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

12 Replies

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

    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

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

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

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

        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

         

  • Ray_Minds's avatar
    Ray_Minds
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

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

      nov month drill down values will not match,

      it will show blank for values that needs to show 1