topic Re: Date Filtering not working in Measure in DAX Commands and Tips

Date Filtering not working in Measure

SUMESHKUMAR22 — Tue, 01 Apr 2025 11:43:59 GMT

Hi team,

Im trying to use the below dax measure but its not filtering as per the time calcualtion slicer . why so & how to resolve?
The last week & this week columns filters from dim date is not coming under filter context.
Whatever weeks are created here, all that is dynamic using rank function, so it can be simply passed in below measure but its not respecting the This week & Last week column. (Relationship is all good 1-many)

Rx Granted = VAR Denominator = CALCULATE( SUM(FCT_Operational_KPIs[VALUE1]), FILTER(FCT_Operational_KPIs, FCT_Operational_KPIs[KPI] = "RX Granted %") ) VAR Numerator = CALCULATE( SUM(FCT_Operational_KPIs[VALUE]), FILTER(FCT_Operational_KPIs, FCT_Operational_KPIs[KPI] = "RX Granted %") ) VAR div_ = IF( ISBLANK(Denominator) || Denominator = 0, 0, DIVIDE(Numerator, Denominator, 0) ) RETURN IF( SELECTEDVALUE('Time_Intelligence'[Time Calculation]) = "Last 7 Days", CALCULATE( div_, KEEPFILTERS(FILTER('DIM Date', 'DIM Date'[This Week] = "This Week")) -- Ensures filter propagates ), IF( SELECTEDVALUE('Time_Intelligence'[Time Calculation]) = "Last week", CALCULATE( div_, KEEPFILTERS(FILTER('DIM Date', 'DIM Date'[Last Week] = "Last Week")) -- Keeps filter context ) ) )

Thanks in advance!

Re: Date Filtering not working in Measure

bhanu_gautam — Tue, 01 Apr 2025 11:59:44 GMT

SUMESHKUMAR22 , Try using

dax
Rx Granted =
VAR Denominator =
CALCULATE(
SUM(FCT_Operational_KPIs[VALUE1]),
FILTER(FCT_Operational_KPIs, FCT_Operational_KPIs[KPI] = "RX Granted %")
)

VAR Numerator =
CALCULATE(
SUM(FCT_Operational_KPIs[VALUE]),
FILTER(FCT_Operational_KPIs, FCT_Operational_KPIs[KPI] = "RX Granted %")
)

VAR div_ =
IF(
ISBLANK(Denominator) || Denominator = 0,
0,
DIVIDE(Numerator, Denominator, 0)
)

RETURN
SWITCH(
SELECTEDVALUE('Time_Intelligence'[Time Calculation]),
"Last 7 Days",
CALCULATE(
div_,
FILTER('DIM Date', 'DIM Date'[This Week] = "This Week")
),
"Last week",
CALCULATE(
div_,
FILTER('DIM Date', 'DIM Date'[Last Week] = "Last Week")
),
div_ -- Default case if no match
)

Re: Date Filtering not working in Measure

SUMESHKUMAR22 — Tue, 01 Apr 2025 12:26:50 GMT

HI bhanu_gautam , I tried this approach earlier but Its the same no change in the filtering showing all dates.

Re: Date Filtering not working in Measure

sjoerdvn — Tue, 01 Apr 2025 14:05:16 GMT

There's a lot going here, and the images are not readable. Most important thing here is that the way you are using variables here will not work. The calculation is done when the variables are defined, the CALCULATE statements will not alter the value. Try something like below.

Rx Granted = IF(SELECTEDVALUE('Time_Intelligence'[Time Calculation]) = "Last 7 Days", CALCULATE(DIVIDE(SUM(FCT_Operational_KPIs[VALUE]),SUM(FCT_Operational_KPIs[VALUE]), 0) ,FCT_Operational_KPIs[KPI] = "RX Granted %" ,KEEPFILTERS('DIM Date'[This Week] = "This Week")) -- Ensures filter propagates + 0) ,IF(SELECTEDVALUE('Time_Intelligence'[Time Calculation]) = "Last week", CALCULATE(DIVIDE(SUM(FCT_Operational_KPIs[VALUE]),SUM(FCT_Operational_KPIs[VALUE]), 0) ,FCT_Operational_KPIs[KPI] = "RX Granted %" ,KEEPFILTERS('DIM Date'[Last Week] = "Last Week")) -- Ensures filter propagates + 0) )

Re: Date Filtering not working in Measure

SUMESHKUMAR22 — Fri, 04 Apr 2025 09:17:35 GMT

HI This solution works as expected but whe I put +0 then it will not respect the TIme calcualtion filter & instead show all data & as I remove the + 0 then it works fine but is there a way we can put 0 for mar 31, Apr01, Apr2 in the below figure.

Data for these 3 dates looks like below as you could see no channel so probably need to figure out a way to handle blank category as it shouldnt be shown on dashboard.

Thanks!