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 ...
  • 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

     

     

     

     

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