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 activity.

  • Enterprise contains a list of unique enterprise number.
  • Establishment contains the list of unique establishment units number per enterprise number. An enterprise number can have 0,1,2 or more establishment units
  • Finally, activity contains a column (entity_number) which contains either establishment unit number or enterprise number, and the activity code(s) associated to it.

An enterprise number can have 0,1,2 or more activity codes, so can an establishment unit number. If an enterprise number has 5 activity codes and its unique establishment unit has 10 activity codes, the total activity codes for this enterprise number should be 15, as an establishment unit is always attached to an enterprise number.

When I drag the fields into a table with enterprise number, establishment unit number and activity code as columns, I need to see all the activity codes associated to the enterprise number, with the establishment unit number being blank for these rows, and then the establishment unit number with the activity codes associated. How can I create the relationships for that? 

Here are pictures of my current data model:


Many thanks for your help already,

 

Jay

 

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.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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 , 

       

      First of all, many thanks for your time and your complete reply, I really appreciate it ! 

      You are fully right about the structure of the three tables, it is exactly how you guessed it. Actually the activity table only contains entity number but I computed the columns "enterprise number" and "establishment number" as they are built differently. 

      What is in your final output is almost 100% what I am looking for because the ultimate goal is to weight the activity codes across an enterprise. If an enterprise and its establishment unit have 10 activity codes and the same code comes back 8 times within the total of 10, we can assume the activity covers 80% of the total activity of the company, therefore this activity code is considered really important. So it would be a "count activity code by enterprise number"/ "total rows for this enterprise number"

       

      Please note activity_code and NACE_code are the same

       

      However, I can not reproduce it in my dataset. 

      I give you an example here below: 

       



      I 
      Enterprise 0657.940.112 has 1 establishment unit but it does not appear when I filter on that enterprise number. The relationship between Enterprise and Establishment is well established (1 to many, single direction). Any idea of what is happening? 

      Again, many thanks for your precious help

       

      Kind regards,

       

      Jay

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous ,

        May I ask which column you used to create the slicer?
        After my testing, the reason I can think of is that you created the slicer using the enterprise_number column in the Activity table instead of the enterprise_number column in the Enterprise table.

        If the enterprise_number column used in your table visual is the same column as the enterprise_number column used in your slicer, then DAX will not work, just 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.