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
comish4lif2
Advocate II
Advocate II

Table Relationship Excluding Values

I have a table (EXAM)  with (for example) 100 unique Identifiers and some columns of data (1s and 0s for an EXAM). In a second table I have a subset of those IDs, but the table may have multiple rows for each ID with the rows varying by the column condition.

There is a proper 1-Many relationship from the EXAM to the CONDITIONS based on the identifier.

 

If I drop fields from both tables into a table visual, I get the expected number of rows (more than 100).

 

But, what I am not getting - at least not in all cases - the table visual is not showing the proper value for EXAM. It is showing an empty/null column and not a 1 or a 0. Especially bad when I am trying to track the EXAM = 1 and it is not there. For some rows, a value is displayed.


Can anyone explain this phenomenon and help me fix it?

6 REPLIES 6
PawelWrona
Helper I
Helper I

If I understand you correctly, you simply miss rows in final table, which are not included in CONDITIONS table. If so, assuming that your EXPORT table has IDs coming from Master table, in the fields section for your table visual, expand the options for IDs and select to "Show rows with no data".

lbendlin
Super User
Super User

Read about AutoExist AutoExist and Normalization - SQLBI

 

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

 

I appreciate your response. I don't think it is an issue of auto exist.

 

If you look at the datatable on it's own, I get 318 rows that have that specific value.

If I join that first datatable with a second datatable, and create a table visual, I only get 250 values. BUt I do get the row based on the member ID as a key.


For security reasons, I am unable to provide my PBIX file.

Please provide sanitized sample data that fully covers your issue. This does not have to be your actual data.
Please show the expected outcome based on the sample data you provided.

OK, here's a very simple version. The Master table has an ID for all MBR. The Conditions table has a Many-1 relation with the Master. A member can have 0, 1, or more Conditions.
The Export Summary should have a row for each row in the Master and any matching data in the Conditions. 

comish4lif2_1-1676500102127.png

The desired result would look something like this:

comish4lif2_2-1676500438370.png

 

 

OK, I think I have solved the issue in my real (much more complex) example. Turning on Show Items with No Data fixed the sample setup shown above.

But, what I think has fixed the original issue is the ordering of the columns. I had some columns from table 1, then some from table 2, more from table 1 and finally some from table 2. If the columns are ALL set to Don't Summarize, BI is essentially performing a "group by" on each column, and by having them in the "wrong" order, I was getting unintended consequences.

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.