topic Looking up value from related table matching 2 columns in DAX Commands and Tips

Looking up value from related table matching 2 columns

herbs — Tue, 14 Jun 2022 18:30:53 GMT

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

Re: Looking up value from related table matching 2 columns

Jihwan_Kim — Wed, 15 Jun 2022 02:15:45 GMT

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] ) ) )

Re: Looking up value from related table matching 2 columns

herbs — Wed, 15 Jun 2022 15:10:26 GMT

Hi,

thanks for the reply.

I have the 2 tables already - its the lookup query that is needed.

Thanks

Re: Looking up value from related table matching 2 columns

v-zhangti — Fri, 17 Jun 2022 08:08:34 GMT

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.

Re: Looking up value from related table matching 2 columns

herbs — Sun, 26 Jun 2022 10:07:47 GMT

Thanks will give this a go and report back

Re: Looking up value from related table matching 2 columns

herbs — Mon, 27 Jun 2022 18:34:02 GMT

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