cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

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:

CADateLASTNONBLANK      KW Predicted    ValueForecast Reliability Score
CA414Tuesday, December 15, 2020101.713 101.713 
CA415Tuesday, December 15, 2020100.036 100.036 
CA414Wednesday, December 16, 2020101.548 101.548 
CA415Wednesday, December 16, 2020100.603 100.603 
CA414Thursday, December 17, 2020101.266 101.266 
CA415Thursday, December 17, 2020100.31 100.31 
CA415Saturday, December 19, 2020112.3112 A - Confirmed by source
CA415Wednesday, March 3, 2021114.312 A - Confirmed by source
CA415Tuesday, March 16, 2021119.315 C - Undetermined
CA415Tuesday, June 1, 2021114.81-4.5 B - Draft
CA415Wednesday, June 2, 2021105.81-9 B - Draft
CA414Thursday, June 3, 202185.166-16.1 A - Confirmed by source
CA415Friday, June 4, 2021103.81-2 B - Draft

 
On requested I've added 
additional info:

PBIX file as example. PBIX File


1 ACCEPTED SOLUTION
Super User II
Super User II

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]))

View solution in original post

6 REPLIES 6
Super User II
Super User II

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]))

View solution in original post

@d_gosbell  Thank you very much, this solved my issue.

 

Christmas is early this year 🙂

Helper II
Helper II

@amitchandak @AllisonKennedy 


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. 

Super User IV
Super User IV

@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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

The calculation returns a error, I've attached a pbix sample file.

 

Could have a look at this?

 

 

Super User III
Super User III

@Chimsie Sorry, I don't understand your question. Can you provide a sample of what you're looking for as final result?



?? Check out my March Madness Report??


Has this post solved your problem? Please mark it as a 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. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors