Compiling data from various criteria (two columns)

[Strongbuck]

The data I am importing has two columns that determines what company the item is related to.  I am trying to create a Power BI graphic that reports the count of data about how long the companies are taking to close the issues.   Please see the data below.

Issues Table:

Issue ID     Assignee Type     Assignee_ID        Status            Open Date       Close Date

1                Company             123456                Closed          1/1/23               6/1/23

2                 User                     888777333         Closed           2/1/23               5/1/23

3                 Role                     554332               Closed           3/1/23                4/1/23 

4                 <blank>             <blank>               Open             1/1/23

Depending on what value is in the assignee_type column, different tables need to be used for the lookup so the correct data is returned.  I am looking to report this data by Company name.

Company Table:

Company ID       Company Name

123456                IBM

747474                Microsoft

User Table:

User ID            User Name         Company ID      Role ID

888777333       John Smith         747474              554332 

888777334       Mary Jones         123456              554332 

888777335       Patrick McGraw   888888              554223  

Role Table:

Role ID          Role Name

554332          Contractor          

554223           Manager

Any ideas on how to do this is appreciated.  The main piece I need help with is how to retrieve the company Id using two columns (assignee_type and assign_id)  I've tried DAX statements but could not get it to work.  Is there a way to load the data to model the data so I can do what I need?

[v-stephen-msft]

Hi @Strongbuck ,

You can use Merge to join tabls. I'll take Company Table as an example, and so on for the rest of the table.

1.Create a custom column with "Company" in Company Table.

2.Use Merge Queries. Pay attention to the numbers of the matching columns, which should correspond one to one.

3.Expand it to see the corresponding columns.

Merge queries overview - Power Query | Microsoft Learn

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Strongbuck]

Hi Stephen,

I see what you did.  Can you please confirm that this would work for the role data.  For that one, you need to lookup the users associated to the role and then get the company key to join that to the company table.

I'm asking this because the role data is a one to many relationship.