Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |