Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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 |
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
Dear @v-jiascu-msft
Thank you so much Dale for taking time and effort to explain the solution in a detailed manner. The issue is now resolved.
Appreciate the help and support 🙂
Keep doing great work!
Regards,
Ram
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |