DAX Expression for comparing rows

[sentsara]

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?

PM	Rate Level	Scenario Rate	Current Forecast	Rate Level Current
CH_ABC	1	67.52%	62.94%	6
CH_ABC	2	66.05%	62.94%	6
CH_ABC	3	65.51%	62.94%	6
CH_ABC	4	64.47%	62.94%	6
CH_ABC	5	64.46%	62.94%	6
CH_ABC	6	61.52%	62.94%	6
CH_ABC	7	59.51%	62.94%	6
CH_ABC	8	57.66%	62.94%	6
CH_ABC	9	0.00%	62.94%	6
CH_GDB	1	91.93%	86.88%	4
CH_GDB	2	89.40%	86.88%	4
CH_GDB	3	89.28%	86.88%	4
CH_GDB	4	85.27%	86.88%	4
CH_GDB	5	84.25%	86.88%	4
CH_GDB	6	77.28%	86.88%	4
CH_GDB	7	77.27%	86.88%	4
CH_GDB	8	76.23%	86.88%	4
CH_GDB	9	0.00%	86.88%	4

[TomMartens · Accepted solution]

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]

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