Issues with LOOKUPVALUE and CALCULATE FIRSTNONBLANK FILTER because reference table has multiple rows

[Anonymous]

Hi all,

I have a couple of tables:

I want to "transfer" ValueX from Table 2 to Table 1:

I tried this solution but it is not working. I think it's related to Table 2 having multiple rows (one per "Group") for the same time.

Thank you!

[dax · Accepted solution]

Hi @Anonymous , 

I think you could just create a relationship between two tables like below

 Then use these column in table directly, then it should work.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[az38]

Hi @Anonymous 

what exactly solution did you use? if smth like

Valuex =
CALCULATE (
    FIRSTNONBLANK ( Table2[ValueX], 1 ),
    FILTER ( ALL ( Table2), Table2[Time] = Table1[Time] )
)

it should work. This IS the sense of FIRSTNONBLANK() function.

also, if all ValueX with the same time are equal you can use MIN, MAX, AVERAGE, like

Valuex =
CALCULATE (
    MIN( Table2[ValueX]),
    FILTER ( ALL ( Table2), Table2[Time] = Table1[Time] )
)

[amitchandak]

Try

new column in table 1 = maxx(filter(table2, table2[time]=table1[time]),table2[ValueX])