Forum Discussion
Linking Two Tables with Multiple Entries
As I understand this is a relationship problem, but I am unsure how to properly link these items to get it to function correctly.
Both tables have many projects each having their own set of activities.
Table 1 - Has activity budgets, many lines for a single project. A single line for each activity.
Table 2 - Has entries against those same activities for time, will have multiple entries for each activity
Problem I have is the budget does not come through correctly, I get this. Desire to have each budget displayed instead of min, max, average. Can't get it to display the seperate values.
Solutions did not work as desired, in the end power query ammending the tables got a result that worked.
8 Replies
Hi LiNSiCKeL,
On the model page, make sure you have a unique value on a table.
You have a single line for each activity on table 1 and link to field "Activity" of the table.
The relation between will be 1-to-many(*) and it should work !
One way to optimize that is to have a dimension table of unique value of Activity and a fact table that contains all records.
Hope it can help you!
Best regards,
Antoine
Hi,
In Power Query, create a table witha single column of all unique activities. Create a Many to One relationship from the 2 existing tables to this third table. To your visual, drag Activity from the 3rd table.
Hi LiNSiCKeL,
Thank you for reaching out to Microsoft Fabric Community.
Thank you AntoineW, FBergamaschi and Ashish_Mathur for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
Hi @LiNSiCKeL,
We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
You need to create a data model
Create a Projects and an activities Table
Projects =
DISTINCT
UNION (
ALLNOBLANKROW ( Table1[project] ),
ALLNOBLANKROW ( Table2[project] )
)Activities =
DISTINCT
UNION (
ALLNOBLANKROW ( Table1[Activity] ),
ALLNOBLANKROW ( Table2[Activity] )
)Connect each of them one to many to your Table 1 and Table 2
Define two measures
Budget = SUM ( Table2 [Budget Labor Hours]
Actual = SUM ( Table1 [Hours] )
and drop both of them into the values section of the matrix
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Not quite getting it to work.
The actual is now a sum of all activities not each one.
I am not sure where the two measures should have been made, or how the actual measure would sum up each activity? I feel more needs to be done there?
