Using RELATED/LOOKUPVALUE in Many:Many relationship

[Anonymous]

Hello,

I'm aware you can't ordinarily use the RELATED or LOOKUPVALUE functions across tables with a Many:Many relationship, but this is essentially what I need to do.

I'm creating a company wide report that can be filtered to different services. To identify which service a staff member belongs to, the only field I can use is called Assignment Number. Unfortunately the dataset has duplicate entries of Assignment Number, created every time a staff profile has been amended on the system. This prevents me joining the Master Table to the Level 5 Org Table via Assignment Number, Many:1.

To identify the correct instance of Assignment Number I have created a sequence with 1 being the most recent entry, and I filter to this in all of my queries. I wonder if I can use this to 'force' either function to work, eg:

IF('Master Table'[HR - AssignmentSequence]=1, LOOKUPVALUE('L5 Org Lookup'[Org L5],'L5 Org Lookup'[Assignment Number],'Master Table'[HR - AssignmentNumber]), 0)

This query does not work, but it demonstrates what I'm trying to do. Can anyone help or think of another solution?

Tiff

[v-juanli-msft]

Hi

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft]

Hi @Anonymous 

Assume you have example as below

What are you looking for?

Create a column in Master Table, like

Assignment Number	Org L5 created
1	d,a,g
2	e,b,h

Right?

p>Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

whenever you can't use RELATED or LOOKUPVALUE use combination of FILTER, TOPN and SELECTCOLUMNS(or any X function like MAXX)

the inner will be your filter, which will go into your table and search for your rows.

then you might have multiple rows, so the TOPN will filter the top row (careful, if you have a draw TOPN will return multiple rows)

Then SELECTCOLUMNS will extract your value.

[Anonymous]

Also, solution by @v-juanli-msft that allows you to create a simplified table that you can use with a 1-* relationship is valid.

[v-juanli-msft]

Hi @Anonymous 

If Master Table to the Level 5 Org Table have a many to one relationship,

You could create a column in Master Table,

What's your scenario?

Do you want to create a column in Level 5 Org Table?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Unfortunately, the tables have a Many:Many relationship for the reasons explained above.

I need to create a column in the Master Table that tells me the Level 5 Org.