Data Model to User Table withData Model to User Table with Multiple Roles Multiple Roles

[comish4lif]

I have something that I am trying to figure out. I am trying to recreate some visualizations that were in Excel and are moving to Power BI.

 I have connected to 2 lists from Sharepoint. One contains Workflow Details, the other is User Information.

In the Workflow list, there are IDs that point to different users in the process: SME_ID, Approver_ID, Requester_ID, etc. In the Excel version, they use those IDs to show the names of the people assigned those roles.

I am having trouble recreating that functionality in Power BI. I want to have the data model create a 1-to-Many relationship from the User ID (user list)  to multiple Approver IDs in the workflow (1 user can approve multiple items). That works fine. But I need to have similar relationships between the User ID and the SME_ID and another from the USer ID to the Requester IDs. But Power BI permits only 1 relationship between 2 tables - which makes sense.

But what is the workaround here? Could I create a "Measure" to go get the FullName associated with the SME_ID, and another to get the FullName associated with the Approver_ID? If that's the best possible, can you shoot some sample syntax here?

[v-ljerr-msft]

Hi @comish4lif,

Based on my test, you should be able to create one active and two other inactive relationships between the two table, then use USERELATIONSHIP function to get the corresponding User Name from User Information table.

Approver Name = CALCULATE(FIRSTNONBLANK('User Information'[Name],1))

Requester Name = CALCULATE(FIRSTNONBLANK('User Information'[Name],1),USERELATIONSHIP('User Information'[User ID],'Workflow Details'[Requester_ID]))

SME NAME = CALCULATE(FIRSTNONBLANK('User Information'[Name],1),USERELATIONSHIP('User Information'[User ID],'Workflow Details'[SME_ID]))

Here is the sample pbix file for your reference. 

Regards

[comish4lif]

OK, I started a new table and didn't have any problems with the join. Until I added 2 measures that I created.

Late Flag = IF(
[Days Late]>0,
TRUE(),
FALSE())

When I add that to the list of Values in a table, the join goes bad and it adds all of the extra rows (a Cartesian Product).

It also adds the extra rows when I add in this measure also.

Days to Close = IF(min('Production Approval Workflow'[STATUS]) = "Complete",
datediff(MIN('Production Approval Workflow'[Created]),MIN('Production Approval Workflow'[Date_Prod_Val_Review]),DAY),"")

And for reference, the [Days Late] measure referenced in the Late Flagmeasure is calculated like this:

Days Late = IF( (
min('Production Approval Workflow'[Status_InFlight]) &&
min('Production Approval Workflow'[Production_Date_Requested])<today() &&
not(isblank(min('Production Approval Workflow'[Production_Date_Requested])))),
DATEDIFF(min('Production Approval Workflow'[Production_Date_Requested]),today(),DAY),
BLANK())

Does anything jump out on any of those 3 measures that looks wrong?

[comish4lif]

A co-worker suggested that I create new tables to serve as the source of the name data for the indovidual roles.

So, I created tables using summarize:

SME_IDs = SUMMARIZE('User Information List',[FULLNAME],'User Information List'[Id])

So, now I have a table with an ID and Fullname. I then create a relationship to my Workflow table. It should be many (workflow) to 1 (SME_IDs). PBI allows me to create the relationship.

Now, if I go to my visualization, and add in the SMEID column from WORKFLOW and the ID and FULLNAME columns from SME IDS, data is incorrect. Instead of matching SMEID 103 with ID 103 and FULLNAME Joe Smith, I get every ID and FULLNAME from the SME_IDs table for each row. So, my table goes from 700+ records to thousands.

Anyone have a clue where I went wrong?