Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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.
Also, solution by @v-juanli-msft that allows you to create a simplified table that you can use with a 1-* relationship is valid.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |