Unable to create visuals with a bridge table and many-to-many relationship

[Anonymous]

Hi Everyone,

I am trying to create a data visualization model with two tables. Since only many-to-many relationship exists between these two tables, I created a bridge table (ie. "Item Master" table) to ensure data accuracy. MappingID is the unique key for "Item Master" table and uses as the primary key for all three tables.

However, after establishing relationships between these tables, I created a visual to illustrate "MappingID" from two tables but this visual show nothing due to no relationship is detected. I checked that these "mappingID" fields are correctly mapped. But, I cannot create any visual using fields from "DFC" table and "SKU" table. 

Grateful if any solution can be provided. Thanks!

[amitchandak · Accepted solution]

@Anonymous , This error comes when you try to use Unsummarized columns from both tables DFC and SKU at the same time you will get this error. Make one of them aggregated(sum/min/max etc), then you can use it.

[Anonymous]

Hi @v-alq-msft  and @amitchandak ,

  Thanks for your solutions!

Best regards,

Dicky

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

The error message occurs when columns from two tables which have no direct relationship are summarized. As is suggested by @amitchandak , you may make some aggregations instead. I created data to reproduce your scenario. The pbix file is attached in the end.

DFC:

SKU:

Item Master(a calculated table):

Item Master = 
DISTINCT(
    UNION(
        DISTINCT(DFC[MID]),
        DISTINCT(SKU[MID])
    )
)

Relationship:

You may create two measures like below.

Date Measure = 
CONCATENATEX(
    DFC,
    [Date],
    ","
)

Name Measure = 
CONCATENATEX(
    SKU,
    [Name],
    ","
) 

Result:

Best Regards

Allan

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

[amitchandak · Accepted solution]

@Anonymous , This error comes when you try to use Unsummarized columns from both tables DFC and SKU at the same time you will get this error. Make one of them aggregated(sum/min/max etc), then you can use it.