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

[MichaelBristol]

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!

[Fowmy · Accepted solution]

@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

[MichaelBristol]

Your solution worked famously! Thank you.

[Jihwan_Kim]

Hi, @MichaelBristol 

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
)

https://www.dropbox.com/s/l0tpcho7epxz37b/michaelbristol.pbix?dl=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.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[MichaelBristol]

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!