The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
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)
)
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)
)
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!
@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
)
Proud to be a Super User! |
|
HI @bhanu_gautam , I tried this approach earlier but Its the same no change in the filtering showing all dates.
User | Count |
---|---|
27 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
11 | |
7 |