Forum Discussion

MichaelBristol's avatar
MichaelBristol
Frequent Visitor
5 years ago
Solved

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              ...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    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

     

     

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

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's avatar
MichaelBristol
Frequent Visitor
5 years ago

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!