Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I'm trying to convert a calculated column into a measure so that I am able to use the measure with a slicer,
I want to apply the slicer to the column Forecast Reliability Score. Depending on the selected Forecast Reliability Score (multi-select), the value in column kW Predicted may or may not be added to the total LASTNONBLANK.
LASTNONBLANK =
var CA = [CA]
var curDate = [Date]
var priorMaxDate = CALCULATE(MAX('Forecast Trendline'[Date]), FILTER('Forecast Trendline', 'Forecast Trendline'[CA] = CA && [Date] < curDate && 'Forecast Trendline'[Value] <> BLANK()))
var sum_pv = SUMX(FILTER('Forecast Trendline','Forecast Trendline'[CA]=CA&&'Forecast Trendline'[Date]<=curDate),'Forecast Trendline'[kW Predicted])
return
if([Value] = blank(), sum_pv+CALCULATE(max('Forecast Trendline'[Value]), FILTER('Forecast Trendline', [CA] = CA && [Date] = priorMaxDate)), 'Forecast Trendline'[Value])
Table:
CA | Date | LASTNONBLANK | KW Predicted | Value | Forecast Reliability Score |
CA414 | Tuesday, December 15, 2020 | 101.713 | 101.713 | ||
CA415 | Tuesday, December 15, 2020 | 100.036 | 100.036 | ||
CA414 | Wednesday, December 16, 2020 | 101.548 | 101.548 | ||
CA415 | Wednesday, December 16, 2020 | 100.603 | 100.603 | ||
CA414 | Thursday, December 17, 2020 | 101.266 | 101.266 | ||
CA415 | Thursday, December 17, 2020 | 100.31 | 100.31 | ||
CA415 | Saturday, December 19, 2020 | 112.31 | 12 | A - Confirmed by source | |
CA415 | Wednesday, March 3, 2021 | 114.31 | 2 | A - Confirmed by source | |
CA415 | Tuesday, March 16, 2021 | 119.31 | 5 | C - Undetermined | |
CA415 | Tuesday, June 1, 2021 | 114.81 | -4.5 | B - Draft | |
CA415 | Wednesday, June 2, 2021 | 105.81 | -9 | B - Draft | |
CA414 | Thursday, June 3, 2021 | 85.166 | -16.1 | A - Confirmed by source | |
CA415 | Friday, June 4, 2021 | 103.81 | -2 | B - Draft |
On requested I've added additional info:
PBIX file as example. PBIX File
Solved! Go to Solution.
So the following slight alteration to your column expression should allow it to work as a measure
LASTNONBLANK 2 =
var CA = SELECTEDVALUE('Forecast Trendline'[CA])
var curDate = SELECTEDVALUE('Forecast Trendline'[Date] )
var priorMaxDate = CALCULATE(MAX('Forecast Trendline'[Date])
, FILTER(ALL('Forecast Trendline')
, [Date] < curDate
&& 'Forecast Trendline'[Value] <> BLANK()
&& 'Forecast Trendline'[CA] = CA))
var sum_pv = CALCULATE(
SUM('Forecast Trendline'[kW Predicted])
, 'Forecast Trendline'[Date]<=curDate)
return if(ISBLANK(SUM('Forecast Trendline'[Value]) )
, sum_pv+ CALCULATE(max('Forecast Trendline'[Value]), 'Forecast Trendline'[Date] = priorMaxDate)
, sum('Forecast Trendline'[Value]))
So the following slight alteration to your column expression should allow it to work as a measure
LASTNONBLANK 2 =
var CA = SELECTEDVALUE('Forecast Trendline'[CA])
var curDate = SELECTEDVALUE('Forecast Trendline'[Date] )
var priorMaxDate = CALCULATE(MAX('Forecast Trendline'[Date])
, FILTER(ALL('Forecast Trendline')
, [Date] < curDate
&& 'Forecast Trendline'[Value] <> BLANK()
&& 'Forecast Trendline'[CA] = CA))
var sum_pv = CALCULATE(
SUM('Forecast Trendline'[kW Predicted])
, 'Forecast Trendline'[Date]<=curDate)
return if(ISBLANK(SUM('Forecast Trendline'[Value]) )
, sum_pv+ CALCULATE(max('Forecast Trendline'[Value]), 'Forecast Trendline'[Date] = priorMaxDate)
, sum('Forecast Trendline'[Value]))
I've attached a PBIX file as example. PBIX File
Additional explanation:
Column CA: Physical Enviroment
Column Value: Is daily measured kilowatt (kW) data
Column Kw Predicted: Predicted grow in kW in the future
Column Project Reliabilty Score: The Reliabilty of the Kw Predicted grow
Column LASTNONBLANK: is the SUM of Value and Kw Predicted.
What I want to achieve is that I can use a Slicer to filter on the Project Reliabilty Score,
So when I uncheck Undertermined in the slicer the line chart will filter the Project Reliabilty Score: Undertermined and re-calculate the line chart without the prediceted value from Undertermined.
As you can see, when I uncheck undertermined, the calculated value stay the same, 101.
@Chimsie , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Try like
LASTNONBLANK =
var CA = max('Forecast Trendline'[CA] )
var curDate = max([Date])
var priorMaxDate = CALCULATE(MAX('Forecast Trendline'[Date]), FILTER('Forecast Trendline', 'Forecast Trendline'[CA] = CA && [Date] < curDate && 'Forecast Trendline'[Value] <> BLANK()))
var sum_pv = SUMX(FILTER('Forecast Trendline','Forecast Trendline'[CA]=CA && 'Forecast Trendline'[Date]<=curDate),'Forecast Trendline'[kW Predicted])
return
if( isblank([Value]), sum_pv+CALCULATE(max('Forecast Trendline'[Value]), FILTER('Forecast Trendline', [CA] = CA && [Date] = priorMaxDate)), 'Forecast Trendline'[Value])
The calculation returns a error, I've attached a pbix sample file.
Could have a look at this?
@Chimsie Sorry, I don't understand your question. Can you provide a sample of what you're looking for as final result?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
103 | |
98 | |
98 | |
38 | |
37 |
User | Count |
---|---|
152 | |
121 | |
73 | |
71 | |
63 |