Get value from another dataset with a corresponding column column

[Fehrnovic]

I don't know how to phrase this better but I have two datasets "Units" and "Tasks" containing different information. However, they share the columns "TaskName" and "TaskId".

The matrix i use displays information from "Units", including registered hours, for each "TaskName" in the dataset "Units". For each of these tasks in "Units", I want the corresponding field "BudgetHours" from the dataset "Tasks". How is this accomplished? I've tried making a bridge table with "TaskId" but it didn't work, and also I want the "TaskName" shown in the matrix, not the Id.

Here is what my matrix looks like:

As you can see, it doesn't show the budget hours corresponding to the single task, but just the total of all tasks.

I would appreciate any help, thank you so much!

[v-yulgu-msft]

Hi @Fehrnovic,

You should create a bridge table with unique "TaskId" and "TaskName". Then, establish one to many relationship between bridge table and "Units" and "Tasks". Place "TaskName" from bridge table, "registered hours" from "Units" and "budget hours" from "Tasks" into Matrix.

Regards,

Yuliana Gu

[Fehrnovic]

Hey @v-yulgu-msft,

Thank you. This answer makes a lot of sense, but I can't seem to create the brigde table right. I have created multiple bridge tables for a single column using the following code:

ProjectName - Merged = DISTINCT(UNION(VALUES(Units[ProjectName]),VALUES(Projects[Name])))

But how is the bridge table created when I need both the unique "TaskName" and "TaskId" column in the same table?

Thank you very much in advance

[v-yulgu-msft · Accepted solution]

Hi @Fehrnovic,

Since you only want the "TaskName" shown in the matrix, not the Id, there is no need to add "ID" column into bridge table. You just need to establish relationship between bridge table and "Task" and "Units" table based on "TaskName" field.

But how is the bridge table created when I need both the unique "TaskName" and "TaskId" column in the same table?

Please refer to this formula:

Bridge table =
UNION (
    DISTINCT (
        SELECTCOLUMNS ( Units, "id", Units[ProjectID], "name", Units[ProjectName] )
    ),
    EXCEPT (
        DISTINCT (
            SELECTCOLUMNS ( Projects, "id", Projects[ID], "name", Projects[Name] )
        ),
        DISTINCT (
            SELECTCOLUMNS ( Units, "id", Units[ProjectID], "name", Units[ProjectName] )
        )
    )
)

Best regards,

Yuliana Gu