Simple data modelling with 3 tables

[JayDR]

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

[Anonymous · Accepted solution]

Hi @JayDR ,

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.

[JayDR]

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]

Hi @JayDR ,

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.

[JayDR]

Hi @Anonymous ,

Correct, I use the right columns 

Thanks again for your help

Kind regards,

Jay

[Anonymous]

Hi @JayDR ,

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.

[JayDR]

Hi @Anonymous, 

Indeed, there was another relationship from Enterprise to Establishment ! 

Many thanks for your help again !

Kind regards,

Jay