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

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.

Reply
rpm19841
Frequent Visitor

Getting first and second row from related table

I have a related table (1..*) like this:

 

IdName
1Foo
2Bar
3Blah

 

In my main table, i want to select the first and second "Name" columns, e.g, this would be my main table:

SomeFieldSomeOtherFieldRelated1NameRelated2Name
abcdefFooBar

 

Thanks!

6 REPLIES 6
v-stephen-msft
Community Support
Community Support

Hi @rpm19841 ,

 

Try to use ALL function

Related1Name = CALCULATE(MAX('Table'[Name]),FILTER(ALL('Table'),[Id]=1))
Related2Name = CALCULATE(MAX('Table'[Name]),FILTER(ALL('Table'),[Id]=2))

5.png

 

 

Best Regards,

Stephen Tao

 

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

amitchandak
Super User
Super User

@rpm19841 , what is a relationship? do you need it one one side or many side ?

 

as of now it seems like

first name =  maxx(filter(Table1, Table[ID]=1), Table[NAme])

Second name =  maxx(filter(Table1, Table[ID]=2), Table[NAme])

 

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

Thanks for helping with this. As i said below though in response to amitchandak , the "Id" column is identity, not the row number. I want the "first" and "second" rows, not the rows with "Id=1" and "Id=2"

 

In other words, the table could look like this:

Id FK Name
1 100 Foo
2 100 Bar
3 101 Blah
4 101 Paa


So, for FK 100, i want  id's 1 & 2, but for FK 101, i want id's 3&4. So the "first" and "second" row, for each "FK". 

Hi @rpm19841 ,

 

As you explained, you can try to add an index column in Power Query. Then you can get the 'first/second' row.

 

If you want to add an index column with the grouping, refer to

Create Row Number for Each Group in Power BI using Power Query

 

 

 

Best Regards,

Stephen Tao

 

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

 

Hi, i dont see how this is going to help. It's going to order all the rows in the table. I want the first and second row for each FK. The index column as far as i can see will just give all the rows a row number.

 

Let me try and re-explain it..

 

Table 1

- Table1Id (PK)

- SomeOtherTableRow1Name -> this is what i want to set

- SomeOtherTableRow2Name -> this is what i want to set

 

Table 2

- Table2Id (PK)

- Table1Id (FK)

- Name

 

So there is a 1..* relationship. I'm trying to fill in the data for the main table, with the first and second row for each corresponding "many" set in the other table. Does that make sense?

 

If i was going to do this in SQL, i would use ROW_NUMBER() OVER (PARTITION BY Table1Id  ORDER BY Table2Id ASC)

The main table is 1, and the table I'm trying to fetch the first and second rows is the "Many".

 

Table[ID] = 1 won't work, as that'll be the record where ID == 1. I want the "first" record, so i assume there will need to be some TOP/ORDERBY in place?

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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