cancel
Showing results for
Did you mean:
Frequent Visitor

## Lookup and Verify Three Columns on Another Table and Generate a Value

What I am trying to accomplish with no success is the following.

Table 1 (Simplified for example)

Unit Num

 A1111 B2222 C3333

Table 2

Date                     Unit                NumColor

 1/1/2021 A1111 Blue 1/2/2021 B2222 Blue 1/3/2021 C3333 Blue 1/4/2021 A1111 Red 1/5/2021 B2222 Yellow 1/6/2021 C3333 Blue 1/7/2021 B2222 Blue 1/8/2021 C3333 Red

Table 1 Result

Unit Num      Color Value

 A1111 5 B2222 0 C3333 5

I am attempting to create a new column (Color Value on Table 1 Result) on Table 1 that Matches the Unit Num on Table 1 with the Unit on Table 2. It also needs to look up the MAX Date and if Num Color is Red return 5 if True and 0 if False.

I really appreciate you taking out time in your very busy lives to look into this for me. Thank You!

1 ACCEPTED SOLUTION
Super User IV

@MichaelBristol

I created a column that returns the result you are looking for:

``````Color Value =
var __maxdate =
MAXX(
FILTER(
'Table 2',
'Table 2'[Unit] = 'Table 1'[Unit Num]
),
'Table 2'[Date]
)
var __result =
IF(
ISEMPTY(
FILTER(
'Table 2',
'Table 2'[Unit] = 'Table 1'[Unit Num] &&
'Table 2'[Date] = __maxdate &&
'Table 2'[NumColor] = "Red"
)
),
0,
5
)
return
__result

``````

Proud to be a Super User!

4 REPLIES 4
Super User IV

@MichaelBristol

I created a column that returns the result you are looking for:

``````Color Value =
var __maxdate =
MAXX(
FILTER(
'Table 2',
'Table 2'[Unit] = 'Table 1'[Unit Num]
),
'Table 2'[Date]
)
var __result =
IF(
ISEMPTY(
FILTER(
'Table 2',
'Table 2'[Unit] = 'Table 1'[Unit Num] &&
'Table 2'[Date] = __maxdate &&
'Table 2'[NumColor] = "Red"
)
),
0,
5
)
return
__result

``````

Proud to be a Super User!

Frequent Visitor

Your solution worked famously! Thank you.

Community Champion

Please check the below picture and the sample pbix file's link down below.

It is for creating a new column in Table1.

Color Value =
VAR currentunit = Table1[Unit Num]
VAR maxdatetable1 =
CALCULATE (
MAX ( Table2[Date] ),
FILTER ( Table2, Table2[Unit] = currentunit )
)
RETURN
IF (
CALCULATE (
SELECTEDVALUE ( Table2[NumColor] ),
FILTER ( Table2, Table2[Unit] = currentunit && Table2[Date] = maxdatetable1 )
) = "Red",
5,
0
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Frequent Visitor

While your solution looks like it should have worked, it created a Circular Dependency in my table. Thank you so much for looking into this!

Announcements