Forum Discussion
Looking up value from related table matching 2 columns
Evening,
I'm trying to add a value from a related table based on 2 columns values.
First Table (where I need the new column populated with coloured answer)
| Unique Company | Rating 1 | Rating 2 | Rating 3 | Overall | |
| A | (rating) | (rating) | (rating) | (rating) | |
| B | (rating) | (rating) | (rating) | (rating) |
Related Table
| Unique Company | Category | Rating |
| A | Rating 1 | Good |
| A | Rating 2 | Satisfactory |
| A | Rating 3 | Very Good |
| A | Overall | Good |
| B | Rating 1 | Satisfactory |
| B | Rating 2 | Adequate |
| B | Rating 3 | Good |
| B | Overall | Satisfactory |
I have tried using IF, Lookup and Switch but cannot get it quite right due to the multiple rows returning a result and breaking the query.
Any help appreciated
Hi, herbs
You can try the following methods.
Column:
Rating 1 = CALCULATE(MAX('Table 1'[Rating]),FILTER('Table 1',[Unique Company]=EARLIER('Table 2'[Unique Company])&&[Category]="Rating 1"))Rating 2 = CALCULATE(MAX('Table 1'[Rating]),FILTER('Table 1',[Unique Company]=EARLIER('Table 2'[Unique Company])&&[Category]="Rating 2"))Rating 3 = CALCULATE(MAX('Table 1'[Rating]),FILTER('Table 1',[Unique Company]=EARLIER('Table 2'[Unique Company])&&[Category]="Rating 3"))Overall = CALCULATE(MAX('Table 1'[Rating]),FILTER('Table 1',[Unique Company]=EARLIER('Table 2'[Unique Company])&&[Category]="Overall"))Measure:
Color 1 = IF(SELECTEDVALUE('Table 2'[Rating 1])="Good","Blue",IF(SELECTEDVALUE('Table 2'[Rating 1])="Satisfactory","Magenta"))Color 2 = IF(SELECTEDVALUE('Table 2'[Rating 2])="Satisfactory","Purple",IF(SELECTEDVALUE('Table 2'[Rating 2])="Adequate","Brown"))Color 3 = IF(SELECTEDVALUE('Table 2'[Rating 3])="Very Good","Green",IF(SELECTEDVALUE('Table 2'[Rating 3])="Good","Red"))Color 4 = IF(SELECTEDVALUE('Table 2'[Overall])="Good","Orange",IF(SELECTEDVALUE('Table 2'[Overall])="Satisfactory","Black"))Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- Jihwan_Kim
Super User
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
It is for creating a new table.
New Table = ADDCOLUMNS ( VALUES ( Data[Unique Company] ), "@Rating 1", CALCULATE ( MAXX ( FILTER ( Data, Data[Category] = "Rating 1" ), Data[Rating] ) ), "@Rating 2", CALCULATE ( MAXX ( FILTER ( Data, Data[Category] = "Rating 2" ), Data[Rating] ) ), "@Rating 3", CALCULATE ( MAXX ( FILTER ( Data, Data[Category] = "Rating 3" ), Data[Rating] ) ), "@Overall", CALCULATE ( MAXX ( FILTER ( Data, Data[Category] = "Overall" ), Data[Rating] ) ) )Hi,
thanks for the reply.
I have the 2 tables already - its the lookup query that is needed.
Thanks
v-zhangtiCommunity Support
Hi, herbs
You can try the following methods.
Column:
Rating 1 = CALCULATE(MAX('Table 1'[Rating]),FILTER('Table 1',[Unique Company]=EARLIER('Table 2'[Unique Company])&&[Category]="Rating 1"))Rating 2 = CALCULATE(MAX('Table 1'[Rating]),FILTER('Table 1',[Unique Company]=EARLIER('Table 2'[Unique Company])&&[Category]="Rating 2"))Rating 3 = CALCULATE(MAX('Table 1'[Rating]),FILTER('Table 1',[Unique Company]=EARLIER('Table 2'[Unique Company])&&[Category]="Rating 3"))Overall = CALCULATE(MAX('Table 1'[Rating]),FILTER('Table 1',[Unique Company]=EARLIER('Table 2'[Unique Company])&&[Category]="Overall"))Measure:
Color 1 = IF(SELECTEDVALUE('Table 2'[Rating 1])="Good","Blue",IF(SELECTEDVALUE('Table 2'[Rating 1])="Satisfactory","Magenta"))Color 2 = IF(SELECTEDVALUE('Table 2'[Rating 2])="Satisfactory","Purple",IF(SELECTEDVALUE('Table 2'[Rating 2])="Adequate","Brown"))Color 3 = IF(SELECTEDVALUE('Table 2'[Rating 3])="Very Good","Green",IF(SELECTEDVALUE('Table 2'[Rating 3])="Good","Red"))Color 4 = IF(SELECTEDVALUE('Table 2'[Overall])="Good","Orange",IF(SELECTEDVALUE('Table 2'[Overall])="Satisfactory","Black"))Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello
Unfortunately it doesn't work which i think is due to the table it is going into rather than the formula itself (creating a circular reference)
To help:
Table "Existing" as it looks currently
Unique Company A B Table "Ratings" as it looks currrently
Unique Company Category Rating A Care Good A Team Outstanding A Happy Good B Care Adequate B Team Adequate B Happy Poor C Care Outstanding C Team Outstanding C Happy Good I need table "Existing" to look like
Uniqueness Company Care Team Happy A Good Outstanding Good B Adequate Adequate Poor C Outstanding Outstanding Good Thanks again for your help so far
Thanks will give this a go and report back
