cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

@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

``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
4 REPLIES 4
Super User

@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

``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Frequent Visitor

Your solution worked famously! Thank you.

Super User

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.

Visit my LinkedIn page by clicking here.

Schedule a meeting with me to discuss further by clicking here.

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors