Forum Discussion

Rishabh-Maini's avatar
Rishabh-Maini
Helper II
5 years ago

Relationship between three tables

Hi all,

I have three tables Table A, Table B, Table C.

Table A has a 1:M relationship with Table B
Table B has a 1:M relationship with Table C

In a visual, I'm displaying columns from Table B and a measure that calculates sum of values of a column in Table C, but a slicer from Table A is controlling the visual. Something like this:
Name      Sum
a              0
b              4
c              0
where Sum: Sum(Table C[Sales]). This all works fine until i add the slicer from Table A. Ideally, it should show only the selection. But its displaying all the values. Somehow, the slicer is unable to filter the data present in the visual.

For example, if i select "a" from the slicer, this is what i get:
Name   Sum
a            0
b            0
c            0

Am i missing someting here? Are the relationships correct? 

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Rishabh-Maini , the relationship seems fine. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Rishabh-Maini's avatar
      Rishabh-Maini
      Helper II
      5 years ago

      Hi,

      Sorry for the confusion. 
      Let me re-phrase the problem. 

      Here is the data model:


      The problem isn't in the measure itself, the problem comes when I try to make changes in how it behaves. 
      There are two scenarios that are happening.

      Scenario 1:

       



      I want the SUMof the column "value" from Sheet4. There are Names(from Sheet1) for which there exists no data in Sheet 4, and the sum just shows blank. I want it to display 0 instead. With the formula above, the issue is that no matter what I select on the slicer, all the names are displayed with the value for the selection changed. 

       

      When nothing is selected.

       

      When I select "b", only that value changes, but all the other values are still displayed
      Scenario 2:

       



      I also want to calculate Average for a column and display it in the form of minutes and seconds. Similar to the above problem, when I add the formatting option, only the value of the selection from the slicer changes, but everything else is still displayed. 

       

      When nothing is selected

       

      When I select "b", only that value should be displayed.
      Also, If I want to display the blank values as "00:00", how to go about it?

       

       


      Let me know if you have any questions. I was unable to upload the file here, but I can provide a google drive link if need be. 

  • negi007's avatar
    negi007
    Community Champion
    5 years ago

    Rishabh-Maini Hi Rishabh, I have tried to replicate the same thing in my powerbi file and it seems to be working fine. Below is the table relationship structure

     

     

    based on the above relationship, i am able to view below data in visual

     

     

    I am sharing powerbi file for your reference as well. Hope that helps you.

     

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    Hi Rishabh-Maini ,

     

    It may be that the sheet2 table serves as a fact table and also serves as a dimension table. Problems caused by errors in filtering relationships between tables. If it is convenient to provide test data (delete sensitive information), I will answer you as soon as possible.


    Let me know the result immediately, looking forward to your reply.

    Best Regards,
    Henry

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