cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichaelBristol
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/2021B2222Blue
1/3/2021C3333Blue
1/4/2021A1111Red
1/5/2021B2222Yellow
1/6/2021C3333Blue
1/7/2021B2222Blue
1/8/2021C3333Red

 

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
Fowmy
Super User IV
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

Fowmy_0-1619466466943.png

 

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn   Join my Power BI User

View solution in original post

4 REPLIES 4
Fowmy
Super User IV
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

Fowmy_0-1619466466943.png

 

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Website   YouTube    LinkedIn   Join my Power BI User

View solution in original post

Your solution worked famously! Thank you.

Jihwan_Kim
Community Champion
Community Champion

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.

 

Picture5.png

 

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


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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

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! 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors