Forum Discussion

Fehrnovic's avatar
Fehrnovic
Frequent Visitor
8 years ago
Solved

Get value from another dataset with a corresponding column column

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 disp...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
Fehrnovic
Frequent Visitor
8 years ago

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's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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