Relationships in table visual break when rows are not aggregates

[Anonymous]

I'm trying to replicate the output of a simple query from SQL Server within a table visual. All of my joins in Desktop match whats in SSMS. I am able to begin building the table, but once I drag on a dimension that returns multiple items for one entity (i.e., two product names tied to one single store) the table breaks and returns the 'no relationship' error. Of course, the table works if I were to use a 'distinct' aggregation on the dimension. I want to show multiple rows for this entity and not use a matrix visual

Store A	Product Name 1
Store A	Product Name 2

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[kentyler]

The data engine for Power BI does not work like SQL Server, so just replicating a SQL query often does not give the results you expect. 

It sounds like you need a fact table for something like Sales and a dimension table for Store and a dimension table for Product.
If you then have 1/many relationships between Sales and Store, and between Sales and Product, you should be able to drag both store and product from the dimension tables onto a table visual and get the behavior you are looking for.

It's hard to tell from your description, but you have have tried to combine too much in your SQL query data source, try loading the tables for Sales, Product and Store separately into Power BI and setting up relationships there...that should make your problem setting up the table visual easy to solve.

[Anonymous]

@kentyler thanks for the reply. I have modeled the underlying tables directly within Desktop (there is not an SQL being used).

Funny enough, I can get the table to work when using an aggregate (i.e., Last or First)

[mahoneypat]

Not totally clear on the request, but it sounds like CONCATENATEX() would help.  With it, you can turn a table into a scalar value that can be shown in tables/matrices.

List of Values = CONCATENATEX(DISTINCT(Table[Column]), Table[Column], ", ")

If you want line breaks, you can use put a carriage return inside the quotes.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypatthanks for the reply - while this did concatenate properl where needed, it also resulted in many many redundant rows for each entity