topic Re: Calculated table ignoring applied filters in DAX Commands and Tips

Calculated table ignoring applied filters

conjaean — Wed, 07 Dec 2022 10:33:42 GMT

Hi all,

I'm facing an issue with the creation of a calculated table. I have two tables: a DimDate table and a table "DimWerknemer" containing employees including the dates they started and left the company.

The goal is to create a calculated table showing all employees that were active in the selected period of the DimDate table.

The DAX code below is giving a table containing all employees, it seems to be ignoring the applied filters on DimDate[Dag].

ActiveUsers = 

VAR MinDate = 
CALCULATE(MIN('DimDate'[Dag]),KEEPFILTERS(DimDate))

VAR MaxDate = 
CALCULATE(MAX('DimDate'[Dag]),KEEPFILTERS(DimDate))
 
RETURN
CALCULATETABLE('DimWerknemer','DimWerknemer'[InDienstVanaf]<=MaxDate,'DimWerknemer'[InDienstTot]>=MinDate)

Any help would be appreciated.

Re: Calculated table ignoring applied filters

lukiz84 — Wed, 07 Dec 2022 11:21:07 GMT

Hi,

calculated tables are calculated during refresh. There is no filter context during refresh, so your MinDate and MaxDate will always give the min and max date of the whole date table.

Re: Calculated table ignoring applied filters

NanDeb — Wed, 07 Dec 2022 11:32:37 GMT

AMMENDED as I meant FILTER NOT SUMMARIZE

ActiveUsers = VAR MinDate = CALCULATE(MIN('DimDate'[Dag]),KEEPFILTERS(DimDate)) VAR MaxDate = CALCULATE(MAX('DimDate'[Dag]),KEEPFILTERS(DimDate)) RETURN FILTER('DimWerknemer','DimWerknemer'[InDienstVanaf]<=MaxDate,'DimWerknemer'[InDienstTot]>=MinDate)

Re: Calculated table ignoring applied filters

NanDeb — Wed, 07 Dec 2022 11:47:06 GMT

I have a similar concept table in one of my reports.