Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

One slicer/filter for three tables

Hello, 

 

On my dashboard I have a visual1 that combines Table 1 and Table 2 together, and this is joined by a many to many relashionship. On that same page I have another visual2 with data from Table 3. 

All these 3 tables have the a coulmn with the same ID, so I would love to use a slicer to filter by ID on both visuals however, so far, its either visual1 works and the other doesnt and vice versa.  

 

At the moment Im only able to connect 2 tables at at time, When I try to edit relashionship of Table 3 + Table 2 it gives the error "you cant create relashionships between Table 3 and Table 2 because that would produce ambuguity between Table 2 and Table 1. To make this relashionship active, delete or deactivate relashionships between Table 1 + Table 2" I cant delete this relashionship since it joins the Table 1 and Table 2 in my visual1

 

So now Im stuck.

 

Is there a work around this? 

 

Thank you

2 Replies

  • Anonymous , You should join these tables using the common table. Create a common table like ID, date, category etc join with three table (1 to M) and use then 

     

    example

    ID = distinct(union( distinct(Table1[ID]),distinct(Table2[ID]),distinct(Table3[ID]) ))

     

    Try to be in a star schema

    https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

     


    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support

    Hi, Anonymous 

     

    Can you provide partial example data or a simple PBIX file? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.

     

    Best Regards