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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
russellj
Frequent Visitor

Show rows that are NOT in linked tables results

Hello,

 

I have the following example tables:

 

Table1- Course information

CourseID1 CourseName1

CourseID2 CourseName2

CourseID3 CourseName3

 

Table2- Users in courses

UserID1 CourseID2

UserID2 CourseID3

 

Table3 - User information

UserID1 Major1

UserID2 Major2

UserID3 Major3

UserID4 Major4

 

Table1 is linked to Table2 on CourseID to show the users in the course, then Table2 is linked to Table3 on UserID to show further information about the users.

 

I want to show under-represented majors, so in this case, I want Major3 and Major4 to display in say, a multi-row card as it is not present in any courses.

 

How can I show the values NOT present in the filter? "Show items with no data" does not seem to work.

 

Thanks!

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

Hi @russellj ,

 

How are the relationships set up?

What are the cardinalities and cross filter directions?

Below is my sample.

vstephenmsft_0-1677130709334.png

vstephenmsft_1-1677130804018.png

Major3 and major4 are displayed.

 

                                                                                                                                                         

Best Regards,

Stephen Tao

 

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
v-stephen-msft
Community Support
Community Support

Hi @russellj ,

 

How are the relationships set up?

What are the cardinalities and cross filter directions?

Below is my sample.

vstephenmsft_0-1677130709334.png

vstephenmsft_1-1677130804018.png

Major3 and major4 are displayed.

 

                                                                                                                                                         

Best Regards,

Stephen Tao

 

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

 

Thank you, it was my relationship setup. I had Users > Users in Courses setup with the proper one to many, but the cross-filter direction being set to both was breaking it. I changed it to single direction and then with items w/ no data enabled, they did show up. Now I can use items with no data combined with a BOTTOM N by count filter to show the under-represented majors.

 

Thanks for pointing me in the right direction.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors