LOOKUPVALUE unexpected blank value

[Wolf01]

Hello, I need to determine person rank based on estimated full year performance. I tried to use an approach from this post but not fully working in my case Solved: Lookup closest value to a number - Microsoft Fabric Community

Here is sample result of my report:

• Rank 2023 is rank gained for 2023
• Rank limit is a limit for rank gained for 2023
• Full year performance is annualized YTD performace
• Full year perfotmance = CALCULATE(SUM('Person'[YTD performance])/MONTH(TODAY())*12)
• Estimation of Rank Based on Full year performance
• This is the problematic column. What I want to do is determine Rank based on Full year performace. Basically, take the Full year performance, connect it with rank table (provided below) and print Rank.
• Estimation of Rank Based on Full year performance =

var Full year perfotmance = CALCULATE(SUM('Person'[YTD performance])/MONTH(TODAY())*12)
var lowerRange = maxx(filter(all('Rank limits'[Rank limit]), 'Rank limits'[Rank limit] < Full year performance), 'Rank limits'[Rank limit]')
return LOOKUPVALUE('Rank limits'[Rank], 'Rank limits'[Rank limit]' , lowerRange)

• lowerrange is result of var lowerRange
• lowerange =

var Full year perfotmance = CALCULATE(SUM('Person'[YTD performance])/MONTH(TODAY())*12)
var lowerRange = maxx(filter(all('Rank limits'[Rank limit]), 'Rank limits'[Rank limit] < Full year performance), 'Rank limits'[Rank limit]')
return lowerRange

• lower range without var syntax is another problematic column where I do not understand the results. I would expect the same result like from lowerrange measure
• lower range without var syntax = maxx(filter(all('Rank limits'[Rank limit]), 'Rank limits'[Rank limit] < CALCULATE(SUM('Person'[YTD performance])/MONTH(TODAY())*12)), 'Rank limits'[Rank limit])

Table relations:

Person[PersonID] 1<->1 ChosenPeople[PersonID] (one-to-one)

ChosenPeople[Rank] *<->1 Rank limits[Rank] (many-to-one)

These are data from Rank limits table:

Rank	Rank limit
R7	1 681 000
R6	1 155 000
R5	825 000
R4	600 000
R3	0
R2	0
R1	0
R0	0

My questions are:

1) Why I do not get the results I am expecting (see the picture above, red writing) and I have blank values?

2) Why does measure lower range without var syntax give blank values? Is it related to problem number 1?

Thank you for help and forgive me if I am missing something obvious 🙂

[Wolf01]

Hello, does anybody have an idea how to solve my issue? Description and sample file posted below.
I would appreciate any idea. Thank you!

[amitchandak]

@Wolf01 ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Wolf01]

Hello amitchandak, thank you for your interest. Here is pbix file with anonymized data: 

https://drive.google.com/file/d/1_cdmb0eJVFodnWRFgjs0g0n5u_KHwoiW/view?usp=drive_link

I have several problems with the report. I created several pages where I try to demonstrate the problem.

Note: I want to show results just for people from ChosenPeople table. Therefore there is a „all pages“ filter with their IDs.

Page1:

• Why there are not results for each row for Estimation of Rank Based on Full year performance measure?

Page 2:

• Why there are not results for each row for lower range without var syntax measure and why are there more (duplicated) rows? I thought it is just lowerrange measure rewritten without var syntax.

Page 3:

• There is measure Annual performance above/below limit which works fine. But if I use Annual performance above/below limit_v2 where i want to subtract 100%, duplicated rows for all IDs (ignoring report filter) appear. Why?

Page 4:

• I want to use Result measure. Again, more people and duplicate rows appear.

I was able to solve problem from Page 1 by adding duplicate Limit tables somehow but I do not consider it as elegant solution. I have no idea what to do with the rest. I am afraid I am doing some kind of basic mistake 😊

Thank you very much for your time and help.