Request Help with Creating a Report using different tables

[Anonymous]

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 

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

The table "BusinessRolePermit" could be redundant. Try it like below.

There seems some errors in your relationship.

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

The table "BusinessRolePermit" could be redundant. Try it like below.

There seems some errors in your relationship.

Best Regards,

Dale

[Anonymous]

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.

[v-jiascu-msft · Accepted solution]

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

[Anonymous]

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