Referencing/Lookup column value from different column

Hi Everyone,

I am trying to figure out a solution and need expert advise on how to achieve it.

I have two tables Applications and Interface. Application has a column "Fitness_Score" and Interface has two columns "Level_1" and "Level_2". Both the tables are realted by means of some joins.

If I take Level_1 and Fitness_Score in a Table visual, data is something like this.

Level_1 | Fitness_Score

A 1

B 2

C 3

D 4

And If I take Level_1, Level_2 and Fitness_Score

Leve_1 | Level_2 | Fitness_Score

A C 1

B A 2

C B 3

D A 4

Here, I dont want Fitness score to be as per Level_1, it should be as per Level_2. Something like this,

Note that Level_1 and Level_2, here are considered as Parent and Child/ Primary and Dependent

Leve_1    |    Level_2    |    Fitness_Score
A    C    3
B    A    1
C    B    2
D    A    1

Any help is appreciated !

Thanks

Need Help

v-kelly-msft

5 years ago

Hi shubh_kush ,

Create a measure as below:

Measure = 
IF(MAX('Interface'[Level_2]) in FILTERS('Fitness_Score'[Level_1]),
CALCULATE(MAX('Fitness_Score'[Fitness_Score]),FILTER('Fitness_Score','Fitness_Score'[Level_1]=MAX('Interface'[Level_2]))),
IF(NOT(MAX('Interface'[Level_2]) in FILTERS('Fitness_Score'[Level_1])),IF(MAX('Interface'[ID_Level_1]) in FILTERS('Application'[ID_Key]),CALCULATE(MAX('Application'[Fitness_Score]),FILTER('Application','Application'[ID_Key]=MAX('Interface'[ID_Level_1]))))))

And you will see: