DAX ALLEXCEPT not keeping total amount when filtering

[vdburg]

Hi,

I am trying to keep the aggregated amount when applying a filter with the exception of two filters, but it changes the amount anyway.

I am using the following DAX code:

Total Incrementos 1 = calculate(sum(VW_CAMBIOS_COMPROMISO[CAMBIOS]);
ALLEXCEPT(VW_CAMBIOS_COMPROMISO;
VW_CAMBIOS_COMPROMISO[FEC_CAMBIO];
VW_CAMBIOS_COMPROMISO[TIPO_CAMBIO]))

I want the sum of 'CAMBIOS' and the only filters that should apply are 'FEC_CAMBIO' and 'TIPO_CAMBIO'

In the next screenshot you can see that the total amount after applying the two filters ('FEC_CAMBIO' and 'TIPO_CAMBIO') is 4.263, which is correct. 

But it should also keep the amount for 'TECNICA' but as you can see from the screenshot, when I filter by 'DTD' it sums 4236 instead of 4263:

What I finally want is to show the proportion of the filtered item relative to the total.

I hope someone knows how to solve this.

I have also tried the following code without any success:

TOTAL INCREMENTOS2 = CALCULATE(sum(VW_CAMBIOS_COMPROMISO[CAMBIOS]);
ALL(VW_CAMBIOS_COMPROMISO);
values(VW_CAMBIOS_COMPROMISO[TIPO_CAMBIO]);
values(VW_CAMBIOS_COMPROMISO[FEC_CAMBIO]))

[v-ljerr-msft]

Hi @vdburg,

Is "TECNICA" also a column from the table "VW_CAMBIOS_COMPROMISO"?

Could you share a sample pbix file(with just some sample/mock data) which can reproduce the issue, so that we help further investigate on t? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[vdburg]

Hi @v-ljerr-msft, @Vvelarde,

Ok, I made up some sample data to share the file with you:

https://1drv.ms/u/s!AtD1xCJ7-i3LukgqpWWWR29E-n3T

In this case the total shows 95:

And when filtering by tecnica it should keep 95, instead it shows 40:

[Vvelarde]

@vdburg

Hi, try with:

TotalNT = CALCULATE(SUM('Table'[Total]),ALL('Table'[Tecnica]))

With this the measure ignore if you apply a filter(or slicer) to this column in your page.

Regards

Victor

[vdburg]

Hi @Vvelarde,

I understand why you suggested the All function instead of the Allexcept function. But be aware that I intented to use AllExcept because I need to ignore all filters with the exception of two. In the example I gave you I only provided one filter to ignore (Tecnica) for simplicity, but in reality I have 10 filters to ignore and it should apply only on two ('FEC_CAMBIO' and 'TIPO_CAMBIO').

So if I wanted to use 'ALL' I should specify the 10 filters to ignore. Though it doesn´t work either.

I think that the definition given by the docs is exactly what I need from the Allexcept function:

"Removes all context filters in the table except filters that are applied to the specified columns.
This is a convenient shortcut for situations in which you want to remove the filters on many, but not all, columns in a table."

But it doesn´t work

[v-ljerr-msft]

Hi @vdburg, @Vvelarde,

After a few tests, I find that the ALLEXCEPT and ALL function doesn't work only when you want to keep the date filter. If the date filter is ignored, all will work as expected. 

For example, let's assume now we want to ignore all filters with the exceptions of [Tecnica] and [Tipo_Cambio], but not the date filter(Fecha_cambio), then all will work as expected. 

I also tested with other data modes, and it turns out the same issue. However, I cannot find an explanation on it. So hope others( @TomMartens @MattAllington @OwenAuger ) who are more experienced on DAX could explain this. 

Regards

[jimmyswoosh]

Hi @v-ljerr-msft

I've just encountered this issue. Has there been a solution since the last time this problem was addressed? Did you figure out the reason why this function the way it does?

Thanks,

-James

[vdburg]

Thank you @v-ljerr-msft for looking into it. At least I know that I am not doing anything wrong.

[Vvelarde]

@vdburg

Hi, My first suggestion is review if the slicer is selected  to the end.

Regards

Victor

[vdburg]

Hi,

Yes, it is. But it shouldn´t affect anyway. The slicer is one of the two filters that should apply to both 'selected' and 'Total'. It´s 'Tecnica' that shouldn´t change the amount of 'Total'.

[Vvelarde]

@vdburg

Can you share the PBIX to review it?.

You can anonymize or delete columns that are confidential.

Regards

Victor