Cannot Visualize Combined Data from Two Tables Connected via Bridge Table (Many-to-Many is avoided)

[pramoth]

I have two fact-like tables in my Power BI model:

Attendance: Contains attendance records (multiple entries per user)

Members_Billable: Contains project assignments (multiple entries per user)

Both tables have a UserID column (non-unique in both).

To avoid a many-to-many relationship, I introduced a third table:

Users: Contains unique UserID values

I created:

A 1-to-many relationship from Users → Attendance

A 1-to-many relationship from Users → Members_Billable

I want to build a visual table showing:

UserID (from Attendance)

Shift Date (from Attendance)

Project Name (from Members_Billable)

Without dragging any column from the Users table into the visual.

[v-prasare]

Hi @pramoth,

May I ask if you have resolved this issue? If so, Can you please share the resolution steps here. This will be helpful for other community members who have similar problems to solve it faster.

If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum we’ll be happy to assist.
Thank you for being part of the Microsoft Fabric Community.

[v-prasare]

Hi @pramoth,

We would like to confirm if our Super user answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are

[v-prasare]

We would like to confirm if our Super user answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@rohit1991 & @bhanu_gautam , thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are

[rohit1991]

Hi @pramoth 

Create Measure : 

Projects (All) = 
CALCULATE (
    CONCATENATEX ( Members_Billable, Members_Billable[Project Name], ", " ),
    TREATAS ( VALUES ( Attendance[UserID] ), Members_Billable[UserID] )
)

Add the following fields:

• UserID (from Attendance)
• Shift Date (from Attendance)
• Project Name (From Members_Billable)

Output: 

[bhanu_gautam · Accepted solution]

@pramoth 

Verify that you have the following relationships:

Users[UserID] (1) to Attendance[UserID] (many)
Users[UserID] (1) to Members_Billable[UserID] (many)

 Use DAX to create a new table that combines the necessary columns from Attendance and Members_Billable based on the UserID. This can be done using the NATURALINNERJOIN function

CombinedTable =
NATURALINNERJOIN(
SELECTCOLUMNS(
Attendance,
"UserID", Attendance[UserID],
"Shift Date", Attendance[Shift Date]
),
SELECTCOLUMNS(
Members_Billable,
"UserID", Members_Billable[UserID],
"Project Name", Members_Billable[Project Name]
)
)

Once you have the CombinedTable, you can create a visual table in Power BI using the columns from this new table:
UserID (from CombinedTable)
Shift Date (from CombinedTable)
Project Name (from CombinedTable)