topic DAX and external filters in DAX Commands and Tips

DAX and external filters

zoubeyrB — Wed, 11 Sep 2024 09:21:56 GMT

Hello,

I have a small dataset (for training) using the following data model :

I would like to calculate the percentage of sales (quantity) for any country / city related to global sales for Germany. So I used the following expression :

measure15 =
var currentSales = sum(sales[quantity])
var salesGermany = CALCULATE(SUM(sales[quantity]),ALLSELECTED(clients[city]),clients[country] = "Germany")
return
divide(currentSales,salesGermany)

The measure works properly as long as the field clients[city] isn't filtered :

case1 :

case2 :

case 3:

I find th 3rd result a bit puzzling, could you kindly provide an explanation (why the part salesGermany is blank) ?

Thanks in advance,

Re: DAX and external filters

Selva-Salimi — Wed, 11 Sep 2024 10:14:11 GMT

Hi zoubeyrB

I think that you should use all instead of allselected.

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Re: DAX and external filters

zoubeyrB — Wed, 11 Sep 2024 11:26:01 GMT

Hello,

I tried replacing ALLSELECTED with ALL, which led to the following result (measure 14) :

The issue of countries other than Germany disappearing is fixed. However, measure14 calculates the ratio between sales for a given country/city combination and sales for Germany unfiltered on city. That's why if we remove Hamburg we get 69.4 % for Germany (instead of 100%).

Re: DAX and external filters

zoubeyrB — Wed, 11 Sep 2024 12:18:30 GMT

The following expression seems to be working :

measure14 = 

var sales_ = sum(sales[quantity])
var salesGermany = CALCULATE(SUM(sales[quantity]),
                              FILTER(ALLSELECTED(clients[city],clients[country]),clients[country] = "Germany")
                              )
return
divide(sales_,salesGermany)

Re: DAX and external filters

zoubeyrB — Tue, 17 Sep 2024 13:19:59 GMT

Hello,

It does make sense to use ALL instead of ALLSELECT (since the reference is all sales for Germany). I was just trying to make it work for training purposes.

Thanks for your help,