DAX Expression help needed

[sentsara]

Hi,

I need to derive "LATEST Break Even" Dax Expression  based on calculated column "Rate Vs Breakeven"

DataSet name: QBM

Rate Vs Breakeven = if([Current Rate 2019]=0,blank(),if(ISBLANK([Current Rate 2019]),blank(),[Current Rate 2019]-[Monthly Break Even]))

Tried with below Expression and thrown "Expression refers to multiple column.Multiple column cannot be converted into scalar value"

LATEST Break Even=CALCULATE(MAX([Rate Vs Breakeven],ALL('QSI By Measure')))

Basically i need Lastest break even value should be repeated based on Max of "Rave vs Breakeven"

TRENDMONTH	Current Rate 2019	Monthly Break Even	Rate Vs Breakeven	LatestBreakeven	Static Final Rate
1	0.67%		0.67%	0.21%	0.423357664
2	4.29%	5.42%	-1.13%	0.21%	0.423357664
3	7.48%	7.64%	-0.16%	0.21%	0.423357664
4	10.74%	10.54%	0.20%	0.21%	0.423357664
5	12.54%	12.33%	0.21%	0.21%	0.423357664
6		14.14%		0.21%	0.423357664
7		15.46%		0.21%	0.423357664
8		16.48%		0.21%	0.423357664
9		15.93%		0.21%	0.423357664
10		18.35%		0.21%	0.423357664
11		19.42%		0.21%	0.423357664
12		19.61%		0.21%	0.423357664
13		19.61%		0.21%	0.423357664
14		21.87%		0.21%	0.423357664
15		28.71%		0.21%	0.423357664
16		43.36%		0.21%	0.423357664
17		44.34%		0.21%	0.423357664
18		44.34%		0.21%	0.423357664

[Anonymous]

What is  the logic ( not formula) for Rate Vs Breakeven ?

Thanks
Raj

[sentsara]

Hi Rajendran.

"Rate Vs Breakeven" is the Caculated Column.

Logic for "Rate Vs Breakeven" is -->  "Current Rate 2019" - "Monthly Break Even"

Formula: Rate Vs Breakeven = if([Current Rate 2019]=0,blank(),if(ISBLANK([Current Rate 2019]),blank(),[Current Rate 2019]-[Monthly Break Even]))

[Ashish_Mathur]

Hi,

Are those formulas measures or calculated columns?  Share the link from where i can download your PBI file.  Share only relevant data in that file please.

[sentsara]

Hi Ashish,

all are caculated columns.

[Ashish_Mathur]

Hi,

This measure will work

=LOOKUPVALUE([Rate Vs Breakeven],Data[TRENDMONTH],CALCULATE(MAX(Data[TRENDMONTH]),FILTER(ALL(Data),[Rate Vs Breakeven]<>BLANK())))

[sentsara]

Hi Ashish,

I'm not able to get the "Rate Vs Breakeven"  in LookupValueExpression. it seems "Rate Vs Breakeven" is a Measure value.

[Ashish_Mathur]

Hi,

[Rate v/s breakdown] should be a calculated column - as you had stated in your post.

[sentsara]

Yes I'm wrong...it's a Measure..

[Ashish_Mathur]

Hi,

SInce the calculation for that is a mere subtraction, write that as a calculated column formula instead and then write my measure.

[sentsara]

Hi Ashish,

when I tried this by converting into Calculated Column the below error is thrown for "Rate Vs Breakeven1".

[Ashish_Mathur]

I do not know what you are doing.  Delete your existing measure and my measure.  Rewrite your original formula as a calculated column and then write my formula as a measure.