DAX issue

[animebuff]

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 

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

[Anonymous]

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]

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

[Anonymous · Accepted solution]

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.

[animebuff]

thanks @Anonymous 

your solution was perfect

but now I face 2 problem in my offical PBI file,

1. my interaction is not happening between year ( also month ) slicer with this metric

we have 4 years ( 2021,2022, 2023 and 2024) 
whatever year I'm selecting I'm getting all the years

relationship between fact & dimension table is fine

2. for ex. even if fact table not having date surrogate key for month year sep 21 

I still data for sep 21 in the metric,

sep 21 shows 1 or 100% and all region inside sep 21 also shows 1 or 100%

[wini_R]

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]

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]

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?

[Dangar332]

Hi, @animebuff 

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

[animebuff]

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