Forum Discussion

sentsara's avatar
sentsara
Helper II
8 years ago

DAX Expression help needed

 

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"

 

TRENDMONTHCurrent Rate 2019Monthly Break EvenRate Vs BreakevenLatestBreakevenStatic Final Rate
10.67% 0.67%0.21%0.423357664
24.29%5.42%-1.13%0.21%0.423357664
37.48%7.64%-0.16%0.21%0.423357664
410.74%10.54%0.20%0.21%0.423357664
512.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
Need Help

11 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    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.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        This measure will work

         

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

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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

     

    Thanks
    Raj

    • sentsara's avatar
      sentsara
      Helper II
      8 years ago

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