Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
maneesha
Frequent Visitor

Table visualization doesn't show data after blanks of one particular column

I have an excel sheet with three tables.

 

Job Task Table

maneesha_0-1608267569628.png

Custom Field Table

maneesha_1-1608267591763.png

Assignee Table

maneesha_2-1608267628362.png

 

Custom Field table and the Assignee table are connected to Job Task table.

maneesha_3-1608267812394.png

maneesha_4-1608267828651.png

 

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.

 

maneesha_5-1608268953943.png

 

But when the Assignee column is moved to the rightest, the data is visible.

 

maneesha_6-1608269016195.png

 

How do you resolve this problem? Is it because of the relationships? Is there an option to be turned on?

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @maneesha ,

 

Please kindly look at @AllisonKennedy 's detailed explanation.

 

In addition, you could merge these three tables shown below

12.22.1.2.gif

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:

12.22.1.1.PNG

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.

View solution in original post

5 REPLIES 5
v-eqin-msft
Community Support
Community Support

Hi @maneesha ,

 

Please kindly look at @AllisonKennedy 's detailed explanation.

 

In addition, you could merge these three tables shown below

12.22.1.2.gif

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:

12.22.1.1.PNG

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.

Thank you @v-eqin-msft . It worked!

 

Thank you for your detailed explanation @AllisonKennedy 

AllisonKennedy
Super User
Super User

@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. 


Please @mention me in your reply if you want a response.

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
Super User
Super User

@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


Please @mention me in your reply if you want a response.

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.