Forum Discussion

animebuff's avatar
animebuff
Helper I
1 year ago
Solved

DAX issue

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))
 
note:
need DISTINCTCOUNT in actual scenario, for example I have written just COUNT
would be much helpful to replicate the scenario with DISTINCTCOUNT
-----------------------------------------------------------------------------------------------------------------------
 
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
2dec
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi wini_R 

     

    Please try this measure:

    MEASURE =
    VAR _currentRegion =
        SELECTEDVALUE ( 's rate'[region] )
    VAR _ifdenominator =
        COUNTROWS (
            SUMMARIZE (
                FILTER (
                    ALLSELECTED ( 's rate' ),
                    's rate'[region] = _currentRegion
                        && 's rate'[inclusion flag] = "n"
                ),
                's rate'[mk],
                's rate'[region]
            )
        )
    VAR _outcome =
        CALCULATE ( COUNT ( 'S rate'[success] ), 'S rate'[inclusion flag] = "Y" )
            / COUNT ( 'S rate'[success] )
    RETURN
        IF ( _outcome <> BLANK (), _outcome, IF ( _ifdenominator <> 1, 1, 0 ) )
    

    The result is as follow:

     

     

    Best Regards

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

9 Replies

  • Dangar332's avatar
    Dangar332
    Resident Rockstar

    Hi, animebuff 

    Can you clarify you're scenarion, What you expect, it better with some Example.

    • animebuff's avatar
      animebuff
      Helper I

      as I stated above, x-axis has 2 fields, 1st month and then 2rd region

      my default dax is CALCULATE(COUNT('S rate'[success]),'S rate'[inclusion flag]="Y")/COUNT('S rate'[success])

       

      so by default, the data will divide by month on 1st level & divide by region of 2nd level

       

      and now my business have a requirement above this logic,

      they want to see all regions available for every month

      for. example, tho for nov month has data only for region apac, australia and la

      they need to see other region as well with if numerator and denominator is not available (or blank) then it should show 100% or 1 and if numerator not available it should show 0 ( 0 by anything is 0)

       

      this is the result I need to get

      uae - 0

      apac - 1

      europe - 0.5

      la - 0.5

      afracia - 1

      australia - 1

      na - 1

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

      modified dax = CALCULATE(CALCULATE(COUNT('S rate'[success]),'S rate'[inclusion flag]="Y")/COUNT('S rate'[success]),CROSSFILTER(region[region],'S rate'[region],None))

       

      this is the logic I wrote to get every region under a month, I got the regions with this logic but for every region I get same values as that month 

  • wini_R's avatar
    wini_R
    Solution Supplier

    Hi animebuff,

    I believe the following measure should give you what you need:

     

     

    ratio = 
    VAR _numerator = CALCULATE(COUNT('S rate'[success]),'S rate'[inclusion flag]="Y")
    VAR _denominator = COUNT('S rate'[success])
    VAR _result = 
    SWITCH(
        TRUE(),
        ISBLANK(_denominator), 1,
        ISBLANK(_numerator), 0,
        DIVIDE(_numerator, _denominator)
    )
    RETURN _result

     

     

    That's the outcome:

     

    If I'm not missing anything, it appears there are denominators for Africa, Aus, NA and UAE that's why we get zeros in the chart (at least this part of your formula referring to denominator returns the result: COUNT('S rate'[success]) )

    • animebuff's avatar
      animebuff
      Helper I

      if I try ISBLANK(num) && ISBLANK(den),1 as one condition of switch statement which is my actual ask 

      then I get 1 for the months that doesn't even have data, for example if my month columns has extra month like jan of 25, even if there is no data, i still will get 1 by logic

      that will be wrong right?

       

      this logic works when under my region drill down but for month it will show wrong values

      • wini_R's avatar
        wini_R
        Solution Supplier

        Hey animebuff , not sure what you mean exactly but I'm pretty sure this can be easily solved. Can you please provide a data sample that includes the case with extra month?

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi animebuff 

     

    Here are 2 workarounds:

    1:

    Use the default dax:

    Measure = 
    CALCULATE ( COUNT ( 'S rate'[success] ), 'S rate'[inclusion flag] = "Y" )
        / COUNT ( 'S rate'[success] )

    Then right-click the [region] field in the X-axis and select Show items with no data:

    The result is as follow:

     

    2:

    Change the measure as the follow:

    Measure 2 = 
    VAR _AAA = CALCULATE ( COUNT ( 'S rate'[success] ), 'S rate'[inclusion flag] = "Y" )
        / COUNT ( 'S rate'[success] )
    RETURN 
    IF(_AAA<>0,_AAA,0)

    The result is as follow:

     

     

    Best Regards

    Zhengdong Xu
    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
      Helper I

      both methods will show data as 0 for months which doesn't have date surrogate key in fact table

      • Anonymous's avatar
        Anonymous
        Not applicable

        Hi wini_R 

         

        Please try this measure:

        MEASURE =
        VAR _currentRegion =
            SELECTEDVALUE ( 's rate'[region] )
        VAR _ifdenominator =
            COUNTROWS (
                SUMMARIZE (
                    FILTER (
                        ALLSELECTED ( 's rate' ),
                        's rate'[region] = _currentRegion
                            && 's rate'[inclusion flag] = "n"
                    ),
                    's rate'[mk],
                    's rate'[region]
                )
            )
        VAR _outcome =
            CALCULATE ( COUNT ( 'S rate'[success] ), 'S rate'[inclusion flag] = "Y" )
                / COUNT ( 'S rate'[success] )
        RETURN
            IF ( _outcome <> BLANK (), _outcome, IF ( _ifdenominator <> 1, 1, 0 ) )
        

        The result is as follow:

         

         

        Best Regards

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