Forum Discussion

NDRS's avatar
NDRS
Frequent Visitor
2 years ago
Solved

Help with relationships between tables

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's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.