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 af...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • 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.

Dangar332's avatar
Dangar332
Resident Rockstar
1 year ago

Hi, animebuff 

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

animebuff's avatar
animebuff
Helper I
1 year ago

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