topic Re: Dax Measure which ignores one filter is not working in DAX Commands and Tips

Dax Measure which ignores one filter is not working

FilipVDR — Fri, 29 Mar 2024 09:03:35 GMT

To calculate the cars that went out of service (VoertuigDatumUitDienst) in the last year i'm creating this measure.

I want to ignore the CarStatus slicer (VoertuigStatusCode). Though it keeps changing my measure when I filter on VoertuigStatusCode. Any idea's?

AantalAutoUitDienstAfgelopenJaar =

CALCULATE(

DISTINCTCOUNT('d_voertuig'[D_Voertuig_BID]),

ALLEXCEPT('d_voertuig', 'd_voertuig'[VoertuigStatusCode]),

FILTER(

'd_voertuig',

'd_voertuig'[VoertuigDatumUitDienst] >= DATE(YEAR(TODAY())-1,1,1) &&

'd_voertuig'[VoertuigDatumUitDienst] <= DATE(YEAR(TODAY())-1,12,31)

)

Re: Dax Measure which ignores one filter is not working

talespin — Fri, 29 Mar 2024 09:25:21 GMT

hi FilipVDR

Instead of ALLEXCEPT('d_voertuig', 'd_voertuig'[VoertuigStatusCode])

use

REMOVEFILTERS('d_voertuig'[VoertuigStatusCode])

Re: Dax Measure which ignores one filter is not working

FilipVDR — Fri, 29 Mar 2024 09:29:24 GMT

When I change to removefilters, same behavior. When i click on the slicer of "voertuigstatuscode" the measure keeps changing.

AantalAutoUitDienstAfgelopenJaar =

CALCULATE(

DISTINCTCOUNT('d_voertuig'[D_Voertuig_BID]),

REMOVEFILTERS('d_voertuig'[VoertuigStatusCode]),

FILTER(

'd_voertuig',

'd_voertuig'[VoertuigDatumUitDienst] >= DATE(YEAR(TODAY())-1,1,1) &&

'd_voertuig'[VoertuigDatumUitDienst] <= DATE(YEAR(TODAY())-1,12,31)

)

Re: Dax Measure which ignores one filter is not working

talespin — Fri, 29 Mar 2024 09:34:19 GMT

hi FilipVDR

If possible please share pbix file with mock data.

Re: Dax Measure which ignores one filter is not working

talespin — Fri, 29 Mar 2024 09:48:49 GMT

Hi FilipVDR

You also need to remove filter on table.

I have used ALL(Tablename), please do not use only table name, instead use

ALL(Column or column names)

Example ALL('d_voertuig'[D_Voertuig_BID], 'd_voertuig'[VoertuigDatumUitDienst])

ALLSELECTED if you want another slicer to impact this measure.

CALCULATE(

DISTINCTCOUNT('d_voertuig'[D_Voertuig_BID]),

REMOVEFILTERS('d_voertuig'[VoertuigStatusCode]),

FILTER(

ALL('d_voertuig'),

'd_voertuig'[VoertuigDatumUitDienst] >= DATE(YEAR(TODAY())-1,1,1) &&

'd_voertuig'[VoertuigDatumUitDienst] <= DATE(YEAR(TODAY())-1,12,31)

)

Re: Dax Measure which ignores one filter is not working

FilipVDR — Fri, 29 Mar 2024 13:00:44 GMT

I found the solution was easier than I thought:

AantalAutoUitDienstAfgelopenJaar =

CALCULATE(

DISTINCTCOUNT('d_voertuig'[D_Voertuig_BID]),

REMOVEFILTERS(d_voertuig[VoertuigStatusCode]),

year(d_voertuig[VoertuigDatumUitDienst]) = year(today())-1

)