Referencing/Lookup column value from different column

[shubh_kush]

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

[amitchandak]

@shubh_kush , what is the structure of your data ?  Source data is not clear.

[shubh_kush]

@amitchandak, 

Source data is SQL Server on Azure, which I am getting through an API call in JSON format.

Below is the table structure:

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

When I'm populating Level_1, Level_2 and Fitness Score, Fitness_Score is getting populated with respect to Level_1, this is because Level_2 is dependent on Level_1.  But I want data as per Level_2. 

[MiraAciu]

Merge queries  Table1 with Table2  by ID_Key from Table 1 and ID_Level_1 from Table 2, and keep only Level 1 from Table 2. This wil attach Level 1 to Table 1.

Merge queries as New and add Table 2 (with Level2 as participating key) with Table 1 (with Level 1 as participating key and Left Join. Keep only Fitness_Score from Table 1.

[amitchandak]

@shubh_kush , With this structure ID_level_1, is giving score and Level_1 and Level_2 has no role ?

[shubh_kush]

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]

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 · Accepted solution]

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!