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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JoeCrozier
Helper II
Helper II

Help understanding the relationships between tables

I apologize because I'm sure some version of this question has been asked, but I'm having the hardest time with it.

 

I have a report like this:

 

JoeCrozier_0-1664467146694.png

 

It's got three tabs, based on three separate SQL queries (and Power bi queries) of our database.  

 

  1. One tab is based on all the subjects we have in our program
  2. One tab is a query of a database with all the staff associated with all the subjects in our database
  3. The third tab (that I am working on now) is from a query of all the subjects in our database that need reconsent.

The way I envision all three tabs working is that you start in the first tab, filter to the subjects you care about, and then those slicers/filters affect the next two tabs.

 

My problem is:

I dont know how to get the slicers/filters in the first tab to affect the others.  I was able to get the second tab linked by just merging those two queries (there's pretty much 100% overlap) and just using "one" dataset for both.

 

I've tried "manage relationships" and created a relationship between the first query subject mrn and the third query subject mrn... but even though there's a 'relationship' between the two... thats not enough for slicers on the first tab to affect anything on the third tab.   How do I do that?

 

So to repeat/rephrase:   Let's say I use slicers on the first tab to filter to 10 people coming in the next week.  Lets say 1 of those people exists in the third query as someone who needs reconsent.  How do I get only that one guy to show up on the third tab with his info from that query?

 

1 ACCEPTED SOLUTION

You can use Performance Analyzer to get the actual DAX query generated for each visual. Copy the queries for both the visuals you are interested in and paste them into DAX Studio. You can then see exactly which filters are being applied to each visual and it should help you work out why one is returning more rows than the other.

View solution in original post

5 REPLIES 5
johnt75
Super User
Super User

You can use the Sync slicers functionality to have a slicer on the first page sync to the other pages, and you can also choose whether or not to make the slicer visible on those other pages, so if you wanted to you could hide it on pages 2 and 3 but still have the choices impact on the data shown.

@johnt75 Thank you.  I dont THINK this is doing it though, I may be doing something wrong.  What I mean is that I BELIEVE my slicers are already synced across all three tabs:  

JoeCrozier_0-1664471053973.png

 

And if I go look at the table on the third tab, it says a bunch of slicers are currently affecting it:

(I changed the slicer values between taking these screenshots, dates look different in each) 

JoeCrozier_1-1664471206334.png

 

BUT on the first tab I've filtered it so strictly that only 5 people are coming in over the next two days, but on the third tab it still shows all 30+ people.  

 

 

 

You can use Performance Analyzer to get the actual DAX query generated for each visual. Copy the queries for both the visuals you are interested in and paste them into DAX Studio. You can then see exactly which filters are being applied to each visual and it should help you work out why one is returning more rows than the other.

Greg_Deckler
Super User
Super User

@JoeCrozier See if you can post an image of your data model and explain where your slicer value are coming from and where your values are coming from.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler   Thank you for the prompt response.

 

Here is a photo of the model: 

JoeCrozier_0-1664468231922.png

 

What I'd done so far (for the first two tabs), is just open the "staff" query, merge in the subjects query so its one giant dataset, and then for every slicer/table/card on the first two tabs use a variable from the new bigger "staff" query.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors