Forum Discussion
Need help/suggestions modelling my dataset
Hi all,
I'm relatively new to powerbi and would like some help modelling my dataset. I'm working with risk and my team want to be able to view all controls related to a framework, and then within that also view all processess (business dimensions) related to those controls.
When retrieving our data, it is formatted as follows (that includes each control only once):
| Guid | IsShared | OrganizationGuid | BusinessDimensions | FrameworkDimensions |
| Control1 | FALSE | [List] | [List] | [List] |
| Control2 | FALSE | [List] | [List] | [List] |
| Control3 | FALSE | [List] | [List] | [List] |
| Control4 | FALSE | [List] | [List] | [List] |
| Control5 | FALSE | [List] | [List] | [List] |
| Control6 | TRUE | [List] | [List] | [List] |
| Control7 | TRUE | [List] | [List] | [List] |
I can expand the [List] sections into new rows, where it would look as follows (this adds new rows for all the relations for controls):
| Guid | IsShared | OrganizationGuid | BusinessDimensions | FrameworkDimensions |
| Control1 | FALSE | Org1 | FD1 | |
| Control1 | FALSE | Org1 | FD2 | |
| Control2 | FALSE | Org1 | FD1 | |
| Control2 | FALSE | Org1 | FD2 | |
| Control2 | FALSE | Org1 | FD3 | |
| Control3 | FALSE | Org1 | ||
| Control4 | FALSE | Org1 | FD1 | |
| Control4 | FALSE | Org1 | FD2 | |
| Control5 | FALSE | Org1 | ||
| Control6 | TRUE | Org1 | BD1 | FD2 |
| Control6 | TRUE | Org1 | BD2 | FD2 |
| Control6 | TRUE | Org1 | BD3 | FD4 |
| Control6 | TRUE | Org1 | BD4 | FD4 |
| Control7 | TRUE | Org2 | BD1 | FD5 |
| Control7 | TRUE | Org2 | BD1 | FD5 |
Furthermore, we have two more tables for Framework Dimensions and Business Dimensions (mentioning each one only once):
| Guid | Name |
| BD3 | Process3 |
| BD4 | Process4 |
| BD1 | Process1 |
| BD2 | Process2 |
and
| Guid | Name |
| FD5 | Framework 1 |
| FD3 | Framework 2 |
| FD4 | Framework 3 |
| FD2 | Framework 4 |
| FD1 | Framework 5 |
We want to create a report where we are able to filter on Framework. This will show the amount of controls per framework (by using a card), controls that are shared, as well as lists out the controls in a table, also breaking them down by organisation level.
Furthermore, we want to visualise the number of processes (business dimensions) involved in each one. So that when we filter on Framework, it shows a number of processes, a table listing the processes and finally, if you drill down on a specific control from the table it will show the specific processes related to that specific control.
At the moment we are unsure of how to model our data. I am able to filter on framework but it does not have the intended effect of filtering the processes as well as the controls simultaneously.
1. Should we create multiple relationship tables for the relations between i.e.:
a. Controls + Frameworks
b. Controls + Processes
c. Controls + Organisation Level
2. Simply expand all the relations for all controls as well as the original including only distinct controls
3. How should the relationships be handled?
I appreciate the help, and if you aren't able to help me with the specific answer, maybe just show me a proper way of work for modelling this data. (no historical data is involved).
Kind regards,
K
2 Replies
- AnonymousNot applicable
Hi Anonymous ,
Is this the result you were expecting?
Please feel free to share your concerns and requirements in detail if my response cannot meet your expectations.
Best regards,
Lucy Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- AnonymousNot applicable
Hi Anonymous ,
Did the reply I offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.
Thanks for your understanding. Your time and cooperation are much valued by us. We are looking forward to hearing from you to assist further.
Best regards,
Lucy Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.