Forum Discussion

eniX's avatar
eniX
Helper III
8 years ago
Solved

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...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    8 years ago

    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's avatar
eniX
Helper III
8 years ago

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's avatar
v-qiuyu-msft
Community Support
8 years ago

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