Forum Discussion

sentsara's avatar
sentsara
Helper II
8 years ago
Solved

DAX Expression for comparing rows

 

Hi ,

I need help on "Rate Level Current" DAX Expression to built.

 

Requirement:

Step:1 Take "current Forecast" Percentage value by applying filter on currrent "PM" Value 

step:2 check under which "Scenario Rate" its falling and map the minium "Rate Level Value". 

 

Pseudo logic:

IF Current Forecast > Scenario Rate then 1
IF CUrrent Forecast between Scenario Rate then take MIN of "RateLevel"
IF Current Forecast < Scenaro Rate then 9

 

below is dataset and i need to Write DAX Expression for " Rate Level Current". can you please help on this?

PMRate LevelScenario RateCurrent ForecastRate Level Current
CH_ABC167.52%62.94%6
CH_ABC266.05%62.94%6
CH_ABC365.51%62.94%6
CH_ABC464.47%62.94%6
CH_ABC564.46%62.94%6
CH_ABC661.52%62.94%6
CH_ABC759.51%62.94%6
CH_ABC857.66%62.94%6
CH_ABC90.00%62.94%6
CH_GDB191.93%86.88%4
CH_GDB289.40%86.88%4
CH_GDB389.28%86.88%4
CH_GDB485.27%86.88%4
CH_GDB584.25%86.88%4
CH_GDB677.28%86.88%4
CH_GDB777.27%86.88%4
CH_GDB876.23%86.88%4
CH_GDB90.00%86.88%4

 

  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

     

    I used this DAX statement to create a calculated column:

    calc Rate Level Current = 
var currentPM = 'Table1'[PM]
var currentForecast = 'Table1'[Current Forecast]
var smallerScenarioRate =
CALCULATE(
    MAX('Table1'[Scenario Rate])
    ,FILTER(
        ALL('Table1')
        ,'Table1'[PM] = currentPM && 'Table1'[Scenario Rate] < currentForecast
    )
)
return
CALCULATE(
    MAX('Table1'[Rate Level])
    ,FILTER(
        ALL('Table1')
        ,'Table1'[PM] = currentPM && 'Table1'[Scenario Rate] = smallerScenarioRate
    )
)

    This returns these values:

     

    And here is a small PBIX file

     

    Hopefully this is what you are looking for.

     

    Regards

    Tom

     

     

     

     

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    Hey,

     

    I used this DAX statement to create a calculated column:

    calc Rate Level Current = 
var currentPM = 'Table1'[PM]
var currentForecast = 'Table1'[Current Forecast]
var smallerScenarioRate =
CALCULATE(
    MAX('Table1'[Scenario Rate])
    ,FILTER(
        ALL('Table1')
        ,'Table1'[PM] = currentPM && 'Table1'[Scenario Rate] < currentForecast
    )
)
return
CALCULATE(
    MAX('Table1'[Rate Level])
    ,FILTER(
        ALL('Table1')
        ,'Table1'[PM] = currentPM && 'Table1'[Scenario Rate] = smallerScenarioRate
    )
)

    This returns these values:

     

    And here is a small PBIX file

     

    Hopefully this is what you are looking for.

     

    Regards

    Tom

     

     

     

     

    • sentsara's avatar
      sentsara
      Helper II
      8 years ago

      Hi Tom,

      Thanks for taking time and provided with good example. i forgot to say one more thing. Current Forecast is the calculated column which is defined in another Dataset.i think thats the reason circular dependency was detected error is thrown.

       

       

      I'm getting error as : "Circular Dependency was detected:"

       

      calc Rate Level Current =
      var currentPM = 'Table1'[PM]
      var currentForecast = 'Table1'[Current Forecast] -- it comes from QRI DATASET --UNDER QRI Dataset it has derived as caculated column


      var smallerScenarioRate =
      CALCULATE(
      MAX('Table1'[Scenario Rate])
      ,FILTER(
      ALL('Table1')
      ,'Table1'[PM] = currentPM && 'Table1'[Scenario Rate] < currentForecast
      )
      )
      return
      CALCULATE(
      MAX('Table1'[Rate Level])
      ,FILTER(
      ALL('Table1')
      ,'Table1'[PM] = currentPM && 'Table1'[Scenario Rate] = smallerScenarioRate
      )
      )