Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi PBI Folks,
I have a table having a columns for Date, Country & Deaths plus some other columns. Each value in Deaths column represents total/cumulative deaths as on that date.
I want to have a dymanic calculated column which is based on a slicer value and provides total deaths with a lag of X days, where X days is a value based on slicer selection. Below is the DAX formula i have used which successfully gives 7 days lag values, but instead of this fixed 7 days I want a dynamic value to be subtracted after earlier function based on a slicer selection.
I tried using whatif parameter but it does not work for a calculated column, any thoughts how I can achieve a dynamic calculated column based on slicers?
Deaths 7 Days Ago = MAXX(FILTER('Main Tbl','Main Tbl'[Country/Region]=EARLIER('Main Tbl'[Country/Region]) &&
'Main Tbl'[Date]=EARLIER('Main Tbl'[Date])-7),
'Main Tbl'[Deaths])
This is my table, let me know if more info is needed to solve this.
Regards
Vikrant
Solved! Go to Solution.
Thanks David, actually posted this more than a month back & was able to acheive it in a different way. I appreciate your effort to answer a ques so long down in the queue 🙂
Hi @vikrantarora25 -
First off, you cannot do this as a calculated column as those do not re-calculate when a slicer or filter is applied. You want to code this as a measure.
That being said, you can access the value selected on a slicer with SELECTEDVALUE(Slicer[Column]), making your code something like this
Deaths 7 Days Ago =
VAR __Lag =
SELECTEDVALUE ( Slicer[Column] )
RETURN
MAXX (
FILTER (
'Main Tbl',
'Main Tbl'[Country/Region] = EARLIER ( 'Main Tbl'[Country/Region] )
&& 'Main Tbl'[Date]
= EARLIER ( 'Main Tbl'[Date] ) - __Lag
),
'Main Tbl'[Deaths]
)
To then make this a "column", just add it along with the other pertinent data from Main Tbl into a table visual.
Hope this helps
David
Thanks David, actually posted this more than a month back & was able to acheive it in a different way. I appreciate your effort to answer a ques so long down in the queue 🙂
@vikrantarora25 You can remove IFERROR because DIVIDE will take care of it, also instead of using SUM () multiple times, create a separate measure for it, hence increasing readability :
Total Deaths =
SUM ( 'Main Tbl'[Cumulative Cases] )
CFR (Deaths/Total Cases X Days Ago) =
SWITCH (
SELECTEDVALUE ( 'Days Lag Tbl'[Lag Days] ),
7,
DIVIDE (
[Total Deaths],
CALCULATE ( [Total Deaths], DATEADD ( 'Main Tbl'[Date], -7, DAY ) )
),
14,
DIVIDE (
[Total Deaths],
CALCULATE ( [Total Deaths], DATEADD ( 'Main Tbl'[Date], -14, DAY ) )
),
21,
DIVIDE (
[Total Deaths],
CALCULATE ( [Total Deaths], DATEADD ( 'Main Tbl'[Date], -21, DAY ) )
),
30,
DIVIDE (
[Total Deaths],
CALCULATE ( [Total Deaths], DATEADD ( 'Main Tbl'[Date], -30, DAY ) )
)
)
Glad to know you got it working.
One additional point of interest - DIVIDE() has its own built-in error handling, so you should not need IFERROR
DIVIDE( Numerator, Denominator, AlternateResult [if Denominator is 0] )
David
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |