Help with relationships between tables

[NDRS]

Hi All,

Probably a basic question coming here.

I got 2 tables with a many-to-many relatiohship that i would like to combine with a date-table, but got issues with losing relationships between the tables depening on different scenarios. 

Scenario 1:

When relationship looks like this (Many-to-one), my stacked charts works just fine. But my relationship between two colums stop working. 

Scenario 2:

Now I got a Many-to-Many relationship between the two tables, then the table works fine but my stacked chart stopped working.

How do I set up the relationships to get both scenarios to work out? 🙂

Thanks!

[Anonymous · Accepted solution]

Hi, @NDRS 

I'm happy to answer your questions. According to your description, stacked charts work fine when the relationship between tables is Many-to-One; table works fine when the relationship between tables is Many-to-Many. Based on this situation, in Scenario 1, try to create an active relationship for the two tables on the Many side to ensure that DAX in the table can normally read the data in the two tables. If Power BI prompts you that it cannot create an active relationship between the two tables, you can create an inactive relationship and enable this active relationship through the following DAX function:

DAX:

USERELATIONSHIP(ColumnName1, ColumnName2)

This function specifies an existing relationship to be used in the calculation of a DAX expression and will only work within that expression and will not affect other calculations in the model. You can click on the link below to learn more about the use of USERELATIONSHIP. If this idea helps you, that would be great.

https://dax.guide/userelationship/

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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