- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Conversion of a calculated column to measure
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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])
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The calculation returns a error, I've attached a pbix sample file.
Could have a look at this?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Chimsie Sorry, I don't understand your question. Can you provide a sample of what you're looking for as final result?
Please @mention me in your reply if you want a response.
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
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
07-07-2024 01:58 PM | |||
03-10-2020 07:49 AM | |||
12-08-2023 03:12 PM | |||
01-30-2024 08:48 PM | |||
07-16-2024 08:16 AM |
User | Count |
---|---|
128 | |
81 | |
59 | |
57 | |
44 |
User | Count |
---|---|
184 | |
111 | |
82 | |
66 | |
52 |