Help! Dax measure to add a column from another table with Many to Many relation.

[Anonymous]

Hello Guys,

I have 2 tables.

Table 1:

User ID    Shift

Table 2 :

USer ID

How do I add a shift column in table 2 with respect to each user ID's in Table 1?

THanks,

Anand!

[v-eachen-msft]

Hi @Anonymous ,

Could you please share your sample data and excepted result to me if you don't have any confidential Information. Please upload your files to One Drive and share the link here. I am not clear about your data type, it is difficult to give a correct solution.

[sturlaws · Accepted solution]

Hi, @Anonymous ,

not much information you are providing here, so not possible to narrow down what would be the best approach for you.

If you have a relationship between the tables, you can look into the related-function, as long as it only returns one value.

Or you can use the lookupvalue-function

Or you can use something like this

column =
VAR _u2 =
    CALCULATE ( SELECTEDVALUE ( 'Table2'[UserID] ) )
RETURN
    CALCULATE (
        MAX ( 'Table1'[Shift] );
        FILTER ( ALL ( 'Table2' ); _u2 = 'Table1'[UserID] )
    )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.