Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Retrieve value from another table without unique id

I have a pretty large table that has a rough layout similar to this:

Capture.PNG

I want to create a column in that table using values from a table that has a rough layout similar to this:

Capture1.PNG

However, the tables do not have a unique id. I would just make a unique id if table 1 was that small but it is much larger. I simply want to assign a count value from table 2 into table 1 based on the year and group #. So the values will be repeated in table 1. I would like a result similar to this:

Capture2.PNG

How do I go about doing this? I've tried using LOOKUPVALUE in a calculated column as well as in a measure. I am using DirectQuery but when I try to enable  “Allow unrestricted measures in Direct Query Mode," there is no option avilable to me. Others also seem to be encountering this issue: https://community.powerbi.com/t5/Desktop/Lookupvalue-function-not-appearing-Direct-Query-mode/td-p/4...

Thanks in advance!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous  ,

Here is a suggestion:


NewColumn = CONCATENATE( Table[Year], Table[Group] )

 

Create this column in both tables.

 

You should then be able to join these tables with Many to One Cardinality.

Let me know how it goes.

Cheers,
Robin

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

View solution in original post

3 REPLIES 3
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

We can create a measure as below.

Column = 
CALCULATE (
    MAX ( 'Table'[count] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[group] = Table1[group]
            && 'Table'[Year] = Table1[Year]
    )
)

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Hi @Anonymous  ,

Here is a suggestion:


NewColumn = CONCATENATE( Table[Year], Table[Group] )

 

Create this column in both tables.

 

You should then be able to join these tables with Many to One Cardinality.

Let me know how it goes.

Cheers,
Robin

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

Anonymous
Not applicable

Thanks @Anonymous ! That seems obvious now that I think about it... oops!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.