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 ,

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.

      • 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