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
kjkjkjnk
Helper I
Helper I

Data Vanishes if Ordered Differently in Table

I just ran into a bizarre issue with data not displaying in a table if the columns are sorted a certain way.  I have a dataset of three tables, Account, Location, Contact.  

 

They are related like this: 
Contact -> Location (Many to Many, bidirectional) 

Location -> Account (Many(Location) to One(Account), bidirectional) 

 

If there are no contacts for the account, I still want location and account information to display.  I have "Show items with no data" selected.  The data from the account and location tables show up in the visual without issue, until the columns for contacts shows up.  Any data after this is always blank.  However, moving columns to the left of the contacts columns has the data display again. 

Filters are not affected, they show the correct data and will filter correctly, but the data they filter in the visual will be blank. 


Here are some examples: 

 

Example 1, data missing:

Name (Account)Location Name (Location) First Name (Contact)ID (Account) Address (Location)
Name 1Location Name 1(blank, correct) blank (incorrect)blank (incorrect)
Name 2Location Name 2(blank, correct)blank (incorrect)blank (incorrect)


 Example 2, data showing if rearranged:

Name (Account)Location Name (Location) ID (Account)Address (Location)First Name (Contact)
Name 1Location Name 1ID 1Address 1(blank, correct) 
Name 2Location Name 2ID 2Address 2(blank, correct)

 

If I made a filter to sort by ID 1, it would be available in the filter, but the data would be blank in example 1.  

 

Of course I can just rearrange columns to get my data to display, but I still want to resolve the issue if possible, thanks!  

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

Hi @kjkjkjnk ,

 

Here's a sample to help you understand  how relationship to filter in Power BI.

 

If your relationship table contain records which can't map correctly to related table, it will show value as blank. Blank means missing records.

 

Here's my test for your reference.

 

Global table:

vyadongfmsft_0-1669877462682.png

 

Local table:

vyadongfmsft_1-1669877462297.png

 

Relationship:

vyadongfmsft_2-1669877462896.png

 

For this table visual, filter relationships are always passed from one side to many side,there is no local_id where global_id = 3 in global table, so local_id shows blank. However, there is no global_brand_name in global table to map to global_id and local id at the same time, so global_brand_name also shows blank.

vyadongfmsft_3-1669877462522.png

 

So, the correct method should be that the columns form one side table should be placed in front of the columns from many side table in order to filter normally.

vyadongfmsft_4-1669877462526.png

 

Please refer to:Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

 

Best regards,

Yadong Fang

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

2 REPLIES 2
kjkjkjnk
Helper I
Helper I

Hello @v-yadongf-msft !

I now understand the logic, thank you!.  However, because this is just a visual issue, is there any way to rearrange the columns to display everything?  

v-yadongf-msft
Community Support
Community Support

Hi @kjkjkjnk ,

 

Here's a sample to help you understand  how relationship to filter in Power BI.

 

If your relationship table contain records which can't map correctly to related table, it will show value as blank. Blank means missing records.

 

Here's my test for your reference.

 

Global table:

vyadongfmsft_0-1669877462682.png

 

Local table:

vyadongfmsft_1-1669877462297.png

 

Relationship:

vyadongfmsft_2-1669877462896.png

 

For this table visual, filter relationships are always passed from one side to many side,there is no local_id where global_id = 3 in global table, so local_id shows blank. However, there is no global_brand_name in global table to map to global_id and local id at the same time, so global_brand_name also shows blank.

vyadongfmsft_3-1669877462522.png

 

So, the correct method should be that the columns form one side table should be placed in front of the columns from many side table in order to filter normally.

vyadongfmsft_4-1669877462526.png

 

Please refer to:Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

 

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.