issue using same column in two tables

[eniX]

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.

 For A I need: A, B, C

[v-qiuyu-msft · Accepted solution]

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],",")

Best Regards,
Qiuyun Yu 

[eniX]

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 😕

[v-qiuyu-msft]

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