if has on value not displaying correct value

[o59393]

hi all

I am using a dax function to calculate the goal of a facility which can be either a country or a bottler.

The target I use for both facilities are:

Complaints Target Bottler = MAX(matriz_objetivos_bottler[complaints]) 

Target Complaints Country = MAX(matriz_objetivos_country[complaints]) 

I am using a table that drills from bottler to country, problem is that the countries are not displaying the right value.

Example, for KOSCAB bottler, target is 0.106, however it's displaying that target for its 4 countries and they all have different ones.

the dax I use is the following

Target Complaints All2 = IF(HASONEVALUE(facilities[bottler]),[Complaints Target Bottler],[Target Complaints Country]) 
 

Problem seems to be that is not taking the false condition when you drill down the countries.

Can you please advise how to create a proper dax?

Thanks!

[az38]

Hi @o59393 

it seems enough to use CALCULATE()

Target Complaints Country = 
CALCULATE(MAX(matriz_objetivos_country[complaints]))

do not hesitate to give a kudo to useful posts and mark solutions as solution

[o59393]

hi @az38 

It didnt work

I keep getting repeated values,

I am using 2 hierarchies, one for bottler and the other one for country. That's why I defined to targets

I just uploaded the pbix, please take a look at the metric.

Basically it should do, if you select bottler assign the bottler target, if you select country, assign the country target.

Thanks!

[v-lionel-msft]

Hi @o59393 ,

As you can see, bottlers always return MAX(matriz_objetivos_country[complaints]), and there is no good way to solve it through DAX.

Maybe you can consider using other visualization methods, such as showing bottlers and countries separately.

Best regards,
Lionel Chen

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

[o59393]

hi @v-lionel-msft 

On your dax I see you have the true condition which is return the country target when you select the bottler, which is incorrect.

The true condition when you select the facilities [bottler] is the bottler target and the false condition is to return the country target when you do not select the bottler or drill down the country.

Is it not possible to have in one single column both targets in function of the drilldown? 😕

Thanks.

[o59393]

hi @az38  and @v-lionel-msft 

I tried with a nested if like this:

If facility es bottler select the bottler target, if the facility is country select country target.

But as you see it still shows the repeated value.

There is no way on Power BI to show the real target when you drill down?

Thanks.

[v-lionel-msft · Accepted solution]

Hi @o59393 ,

Try this measure:

Target Complaints All2 = 
VAR x = MAX(matriz_objetivos_bottler[complaints])
VAR y = MAX(matriz_objetivos_country[complaints])
RETURN
IF(
    HASONEVALUE(facilities[country]),
    y,
    IF(
        HASONEVALUE(facilities[bottler]),
        x
    )
)

Best regards,
Lionel Chen

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

[o59393]

@v-lionel-msft  thank you!! it worked.

Have a great day.

[o59393]

Here is the pbix

https://1drv.ms/u/s!ApgeWwGTKtFdhi5jE16KVKKMxgsw?e=DAFh5V

Thanks.