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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
eniX
Helper III
Helper III

issue using same column in two tables

I have a huge (~4GB) data source where a part is referenced to cars, where it can be used. Same part can be used in different cars and same car can use different parts (so it's m2m relationship).

In my report I want to have 1 table showing all parts I've got. When I highlight one of them, i.e. "A", I want to see in another table a list of parts, which can be used for cars related to "A". Semantically it means, I want to have a list of alternative parts to A.

Is it possible to achieve this behaviour without loading/copying/referencing the query and without the need of building a cross product? It works when i copy the query, but the size of my data model explodes (over 1GB) and it feels more like a not elegant workaround.

pbi_table.pngpbi_visuals.png

 For A I need: A, B, C

 

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

Hi @eniX,

 

In your scenario, we can concatenate corresponding part to one row, but we aren't able to display corresponding part to separate rows from the same query. 

 

You can create a measure like this:

 

Measure = var temp=CALCULATETABLE(VALUES(Table1[car]),ALLSELECTED(Table1[part]))
return
CONCATENATEX(CALCULATETABLE(VALUES(Table1[part]),FILTER(ALL(Table1),CONTAINS(temp,[car],[car]))),[part],",")

 

q5.PNG

 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
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-qiuyu-msft
Community Support
Community Support

Hi @eniX,

 

In your scenario, we can concatenate corresponding part to one row, but we aren't able to display corresponding part to separate rows from the same query. 

 

You can create a measure like this:

 

Measure = var temp=CALCULATETABLE(VALUES(Table1[car]),ALLSELECTED(Table1[part]))
return
CONCATENATEX(CALCULATETABLE(VALUES(Table1[part]),FILTER(ALL(Table1),CONTAINS(temp,[car],[car]))),[part],",")

 

q5.PNG

 

 

Best Regards,
Qiuyun Yu 

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

I definitely need them in a seperate row, because I want to display additional information. Thanks for pointing out, that it's not possible with Power View.

 

@v-qiuyu-msft, can you tell me then what is my best option to model it? Calculated table/cross product/copying the table/other possibility? I really need to keep my data model at minimal size due to 1GB limit 😕

Hi @eniX,

 

You can filter to keep useful data in Query Editor, apply useful data to data model. Or you can try the DirectQuery if possible. 

 

Best Regards,
Qiuyun Yu 

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Solution Authors