Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have an excel sheet with three tables.
Job Task Table
Custom Field Table
Assignee Table
Custom Field table and the Assignee table are connected to Job Task table.
According to the tables, Job task 2,3 and 4 are assigned with an assignee and Job task 1,2 and 3 are has a date and a budget.
But when the table visualization is created dates of Job task 1 is missing since there's no data in the assignee column for Job 1.
But when the Assignee column is moved to the rightest, the data is visible.
How do you resolve this problem? Is it because of the relationships? Is there an option to be turned on?
Solved! Go to Solution.
Hi @maneesha ,
Please kindly look at @AllisonKennedy 's detailed explanation.
In addition, you could merge these three tables shown below
The whole M syntax :
let
Source = Table.NestedJoin(#"Job Task Table", {"Job Task ID"}, #"Assignee Table", {"Assignee ID"}, "Assignee Table", JoinKind.LeftOuter),
#"Expanded Assignee Table" = Table.ExpandTableColumn(Source, "Assignee Table", {"Assignee ID", "Assignee"}, {"Assignee ID", "Assignee"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Assignee Table", {"Job Task ID"}, #"Custom Field Table", {"Custom Field ID"}, "Custom Field Table", JoinKind.LeftOuter),
#"Expanded Custom Field Table" = Table.ExpandTableColumn(#"Merged Queries", "Custom Field Table", {"Custom Field ID", "Date", "Budget"}, {"Custom Field ID", "Date", "Budget"})
in
#"Expanded Custom Field Table"
Or use NATURALLEFTOUTERJOIN() function to createa a calculated table:
Table =
NATURALLEFTOUTERJOIN (
NATURALLEFTOUTERJOIN ( 'Job Task Table', 'Assignee Table' ),
'Custom Field Table'
)
The final output looks like this:
Please check the pbix file here.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @maneesha ,
Please kindly look at @AllisonKennedy 's detailed explanation.
In addition, you could merge these three tables shown below
The whole M syntax :
let
Source = Table.NestedJoin(#"Job Task Table", {"Job Task ID"}, #"Assignee Table", {"Assignee ID"}, "Assignee Table", JoinKind.LeftOuter),
#"Expanded Assignee Table" = Table.ExpandTableColumn(Source, "Assignee Table", {"Assignee ID", "Assignee"}, {"Assignee ID", "Assignee"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Assignee Table", {"Job Task ID"}, #"Custom Field Table", {"Custom Field ID"}, "Custom Field Table", JoinKind.LeftOuter),
#"Expanded Custom Field Table" = Table.ExpandTableColumn(#"Merged Queries", "Custom Field Table", {"Custom Field ID", "Date", "Budget"}, {"Custom Field ID", "Date", "Budget"})
in
#"Expanded Custom Field Table"
Or use NATURALLEFTOUTERJOIN() function to createa a calculated table:
Table =
NATURALLEFTOUTERJOIN (
NATURALLEFTOUTERJOIN ( 'Job Task Table', 'Assignee Table' ),
'Custom Field Table'
)
The final output looks like this:
Please check the pbix file here.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@maneesha , no, changing cross filter won't fix this problem. The order matters in your table because the relationships act like a Left Outer join, and you don't have every dimension in your fact table (assignees table). Therefore you need to put the dimension (Job Task and Custom Field) tables first in your visualization. This is just how the relationships work, so you have to put up with the order - the fact/values always go last. So in this case, you start with the TaskID - that is your left outer join. It provides all TaskIDs in your data model. As you add columns to this visual, the number of rows may increase or decrease depending on the relationships and if the task exists in the other tables. Since you have 'show items with no data' on, the number of rows won't increase, but you also won't get any dimension info if you've already used a fact value and that task doesn't exist in the fact table.
This doc kind of starts to explain why this behaviour exists, but it doesn't get you all the way there: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data
If you have a good understanding of SQL joins, it uses that same behaviour.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@maneesha Yes, this is because of relationships. Your model view looks very strange - both cross filter direction is NOT recommended for one to many relationships. Also, I'm not sure why you have two assignees for assignee ID? What exactly is the data being represented here?
Typically you need a fact/transaction table that links all your dimensions, and that's how you can build the table visualization.
If you can provide more background info on what you need, that would be awesome. Also, here's my attempt at explaining why unique IDs are important and why to stay away from both cross filter direction:
https://excelwithallison.blogspot.com/search?q=relationships
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy Thank you for the explanation.
I made the relationship to one direction and it did not solve the problem.
*One Job task can have many assignees.
This is a representation of a scenario that I came across while I was working with power BI
Do you have any other explanation to this?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |