Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Simple data modelling with 3 tables

Hello all,  I have a simple problem yet difficult for me to sort out, and I really need your help.  I need to build a data model in PowerBI with 3 tables : establishment, enterprise and activit...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    Since you did not provide sample data, I had to create a sample dataset myself based on your description and screenshots.
    Here are my sample datasets:

    And here is the relationship:


    I see that your Activity table also contains columns for enterprise number and establishment unit number, so I'm guessing your data structure might look something like this. I also used these two columns in the Activity table to create the Table visual object. If your data structure is different from what I'm guessing, a bit of sample data from the three tables and the expected results would be appreciated!

    Then I use table Enterprise to build a slicer:

    And use this DAX to create a measure in table Activity:

     

    Measure = 
IF(
    ISFILTERED(Enterprise[enterprise_number]),
    IF(
        MAX('Activity'[enterprise number]) IN VALUES(Enterprise[enterprise_number]) || MAX('Activity'[establishment unit number]) IN VALUES(Establishment[establishment units number]),
        1,
        0
    ),
    1
)

     

    Then please set it according to the following figure:

    The final output is as below:

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

Correct, I use the right columns 

Thanks again for your help

 

Kind regards,

 

Jay

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

If you have used the right columns but the problem still exists, please check the relationships.
You need to ensure that there is no relationship between the Activity table and the Enterprise and Establishment tables:

Neither can there be other filter paths that can filter from the Enterprise table or the Establishment table to the Activity table, for example:

If such a relationship exists, it can also lead to problems with the DAX results.

If there is no relationship between the tables, but your problem persists, I may need to examine your data structures to find out what the problem is. In that case, you may need to provide a pbix file or sample data that matches your complete data structure.

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous, 

     

    Indeed, there was another relationship from Enterprise to Establishment ! 

     

    Many thanks for your help again !

     

    Kind regards,

     

    Jay