Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

Fowmy_0-1619466466943.png

 

 

 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

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

Fowmy_0-1619466466943.png

 

 

 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Your solution worked famously! Thank you.

Jihwan_Kim
Super User
Super User

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


Go to My LinkedIn Page


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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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