Forum Discussion
Request Help with Creating a Report using different tables
Dear All,
I request your Help in creating a report from different tables.
I have six tables namely BusinessCategories, BusinessPermits, BusinessRolePermit, BusinessRoles, BusinessUserRoles, & BusinessUsers as my input. Using these tables, I have to create a report (expected columns in this report is shown at the end)
BusinessCategories
| Business ID | Business Category |
| NY101 | Person |
| NY102 | Mail Form |
| NY103 | Application |
| NY104 | Accounts |
BusinessPermits
| Permit ID | Permit | Business ID |
| 100 | Enter Person Module (Read-only) | NY101 |
| 200 | Edit a Person | NY101 |
| 300 | Delete a person | NY103 |
| 400 | Enter Member Company Module (Read-only) | NY104 |
| 500 | Edit a company | NY103 |
BusinessRolePermit
| Role ID | Permit ID |
| XBXY-987V-A678-HLFJSFSA6 | 100 |
| GFHY-837F-F725-LIPGSTFSL2 | 500 |
| XBXY-987V-A678-HLFJSFSA6 | 200 |
| LPAR-298R-U364-LPSRKHVY5 | 500 |
BusinessRoles
| Role ID | Roles | Description |
| XBXY-987V-A678-HLFJSFSA6 | Guest | Read-Only Access |
| GFHY-837F-F725-LIPGSTFSL2 | Admin | Read Write Access |
| JKSP-567W-J275-KSPFNXMT8 | User | Read-Only Access |
| LPAR-298R-U364-LPSRKHVY5 | Super User | Read-Only Access |
BusinessUserRoles
| Role ID | User ID |
| XBXY-987V-A678-HLFJSFSA6 | A00201 |
| GFHY-837F-F725-LIPGSTFSL2 | A00202 |
| JKSP-567W-J275-KSPFNXMT8 | A00203 |
| GFHY-837F-F725-LIPGSTFSL2 | A00204 |
BusinessUsers
| User ID | Name | Login |
| A00201 | Raghav Vellanki | rvellanki |
| A00202 | Jason Peterson | Jpeterson |
| A00203 | Alan King | Aking |
| A00204 | Mark Waugh | Mwaugh |
The expected output report should have the following columns:
| Role ID | Roles | Description | Permit | BusinessCategory | Permit ID | Name | Login |
| XBXY-987V-A678-HLFJSFSA6 | Guest | Read-Only Access | Enter Person Module (Read-only) | Person | 600 | Raghav Vellanki | rvellanki |
| GFHY-837F-F725-LIPGSTFSL2 | Admin | Read Write Access | Enter Person Module (Read-only) | Person | 500 | Jason Peterson | Jpeterson |
| JKSP-567W-J275-KSPFNXMT8 | Guest | Read-Only Access | Enter Person Module (Read-only) | Person | 500 | Alan King | Aking |
| LPAR-298R-U364-LPSRKHVY5 | Guest | Read-Only Access | Enter Member Company Module (Read-only) | Member Company | 600 | Mark Waugh | Mwaugh |
The relationships that I built could only retrieve till column PermitID. When I try to pull data for 'Name' and 'Login' columns (.i.e. from BusinessUserRoles Table and BusinessUsers. table) I am getting an error message "Can't determine relationships between the fields". I am not sure where I went wrong, Did I build the relationships wrong or the data model is wrong?
Error MessageDataModel
Hi Anonymous,
The table "BusinessRolePermit" could be redundant. Try it like below.
There seems some errors in your relationship.
Best Regards,
Dale
Hi Rnagalla,
I afraid your mode isn't the best practice in Power BI. Please check out the demo here. Just like in the Data warehouse, one (or many) fact table with many dimension tables.
1. Merge two tables to get a fact table.
2. Recreate the relationships. It's a star relationship. If we want to see the details, we can add the dimensions.
3. Almost all the fields can be added into the table visual now.
Best Regards,
Dale
4 Replies
Hi Anonymous,
The table "BusinessRolePermit" could be redundant. Try it like below.
There seems some errors in your relationship.
Best Regards,
Dale
Dear v-jiascu-msft
Thank you so much for responding to my query. I have tried using the data model you suggested. However I am recieving a error in relationship.
When I merge "Permit ID" column from "BusinessRolesPermit" table to "BusinessRoles" table, the "RoleID" column which has unique values earlier is now getting duplicate values. The "RoleID" column in "BusinessUserRoles" table already has duplicate values. Hence I am recieving a error message, while trying to build relationship between "BusinessRoles" table and "BusinessUserRoles" table.
Old BusinessRoles Table
Role ID Roles Description XBXY-987V-A678-HLFJSFSA6 Guest Read-Only Access GFHY-837F-F725-LIPGSTFSL2 Admin Read Write Access JKSP-567W-J275-KSPFNXMT8 User Read-Only Access LPAR-298R-U364-LPSRKHVY5 Super User Read-Only Access New BusinessRoles Table (after merging Permit ID)
Role ID Roles Description businessrolepermits.Permit ID XBXY-987V-A678-HLFJSFSA6 Guest Read-Only Access 100 XBXY-987V-A678-HLFJSFSA6 Guest Read-Only Access 200 GFHY-837F-F725-LIPGSTFSL2 Admin Read Write Access 500 LPAR-298R-U364-LPSRKHVY5 Super User Read-Only Access 500 JKSP-567W-J275-KSPFNXMT8 User Read-Only Access Error Message Screenshot
Please suggest on how to overcome this error. Appreciate all your support. I can't really stress enough on how much your response helped me. Thank you again.
Rnagalla.
Hi Rnagalla,
I afraid your mode isn't the best practice in Power BI. Please check out the demo here. Just like in the Data warehouse, one (or many) fact table with many dimension tables.
1. Merge two tables to get a fact table.
2. Recreate the relationships. It's a star relationship. If we want to see the details, we can add the dimensions.
3. Almost all the fields can be added into the table visual now.
Best Regards,
Dale