Forum Discussion

shubh_kush's avatar
shubh_kush
Helper I
5 years ago
Solved

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 Interfa...
Need Help
  • v-kelly-msft's avatar
    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:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

shubh_kush's avatar
shubh_kush
Helper I
5 years ago

Along with ID_Level_1, there is another column, ID_Level_2 for Level_2 but not getting used for any relationship.

Level_1 and Level_2 is in the DB to showcase the dependency of associated Interfaces of Level_2 with Level_1 Interfaces. Though the Interface name is same.  I have a report in which there are two tabs, one showing each Level.  And want to assign Fitness_Score to Level_2 in a exact sequence it is associated with Level_1.

 

Idea is to show Level_1 in a Donut chart with its fitness score. Level_2 in another donut chart but the fitness score must be as is in Level_1.

 

 

shubh_kush's avatar
shubh_kush
Helper I
5 years ago

Considering this scenario: 

 

Application:
ID_Key    |    Fitness_Score
 100                    1
 101                    2
 102                    3
 103                    4

Interface:
ID_Level_1   |   Level_1   |   Level_2
 100                   A                 C
 101                   B                  A
 102                   C                  B
 103                   D                  A

 

Let say, If I show Fitness_Score and Level_2 for A, it will give 2 since, Level_2(A) is mapped with Level_1(B) and it has ID_Level_1: 101, for which the Fitness_Score is 2 in Application Table.

(Marked Red above)

 

For the above query, I want to have 1, Since A in Level_1 has 1 Fitness_Score. So for every occurance of A in Level_2, the Fitness_Score must be 1. 

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    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:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!