PowerBI DAX Filtering by MIN/MAX Date from Slicer

Question

I have a table named 'Master Query' that holds a column called 'RegisterDate'. My PowerBI dashboard has Date Slicer that allows the user to control the data shown on a Matrix table. I am trying to create a new summarised table by filtering the MIN and MAX Date ranges that the user has chosen in the slicer.

I have tried this DAX code below but it seems the dates are not being picked up as all rows are returning rather than only summarising the rows within the desired date range:

HE KPI Card = 
VAR _MinDate = CALCULATE(MIN('Master Query'[RegisterDate]),ALLSELECTED('Master Query'[RegisterDate]))
VAR _MaxDate = CALCULATE(MAX('Master Query'[RegisterDate]),ALLSELECTED('Master Query'[RegisterDate]))
RETURN
CALCULATETABLE(SUMMARIZE('Master Query',Students[ID],'KPItarget'[Description],"Mark1",SUM('Master Query'[Mark1]),"Mark2",SUM('Master Query'[Mark2]),"AuthCount",SUM('Master  Query'[AuthorisedCount])),FILTER('Master Query','Master Query'[RegisterDate] >= _MinDate &&  'Master Query'[RegisterDate] <= _MaxDate))

Main part of DAX code:

HE KPI Card = 
VAR _MinDate = CALCULATE(MIN('Master Query'[RegisterDate]),ALLSELECTED('Master Query'[RegisterDate]))
VAR _MaxDate = CALCULATE(MAX('Master Query'[RegisterDate]),ALLSELECTED('Master Query'[RegisterDate]))
RETURN
FILTER('Master Query','Master Query'[RegisterDate] >= _MinDate &&  'Master Query'[RegisterDate] <= _MaxDate))

How would I go about doing this? I am unsure why my DAX is not working...perhaps there is a better way of doing it within PowerBI

msta42a msta42a · Accepted Answer · 2021-06-14T09:23:35

Calculated Table are "calculated" once at refresh time.

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-calculated-tables

Calculated tables are recalculated if any of the tables they pull data from are refreshed or updated, unless the table uses data from a table that uses DirectQuery

If you want to only show summarized data then a better idea is to create new measures with 0/1 output. Put everything that you need to table visualization -> columns and measures + add to filter pane this new measure to show only 1 (based on your date condition).

If this is not the case, then describe in detail what do you want to achieve.

PowerBI DAX Filtering by MIN/MAX Date from Slicer

1 Answers