cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BIBen
Helper I
Helper I

How to show which audits belong to a person in drill-down

(Very much learning Power BI; thank you for your patience, in advance. I always award "Solution" to solutions.)

 

The Setup

I have multiple audits belonging to each of several persons; a person, in this case, is called "Provider"

I want to start with displaying a list of all of the Providers.

I want to click a provider's name, and see only the audits they are the subject of.

That's it, and I can't seem to get the hang of it. 

 

The Attempt

I am pretty close:

BIBen_0-1635277022013.png

But, because I am using the Chart Number column, as-is, when I click the "+" sign to drill-down, I get all the chart audits, with data for the appropriate chart number populated:

BIBen_1-1635277315721.png

 

The Tables

cr508_providerkey and ProviderRosterKey make the one-to-many relationship between the two tables. I am assuming (and have failed in my several attempts) that I need to make a measure or column out of 'Chart Number':

BIBen_2-1635277647480.png

 

The Goal

Click the "+" sign next to the Provider's name, and see a list of chaerts where their ProviderRosterKey is listed under cr508_providerkey

 

Sorry if this doesn't help (PowerApps example to maybe help visualize the solution I am looking for better)

If I were doing this in Power Apps, it would be a nested gallery, with:

   - the parent gallery items property being 'Encounter Provider Roster'.'Provider Name'

   - and the inner gallery items property being something like Filter(Charts, ThisItem.cr508_providerkey = ProviderRosterKey, 'Chart Number')

 

(I've been doing a lot of Power Apps lately, and now it is time to work on the reporting aspect, which brings me here.)

 

The Very Sincere Thanks

Thank you, sincerely, for your time and help. I recognize that I would not be where I am in my career without the assistance of my excellent Forum leaders andsolution-providers, and I know it.

5 REPLIES 5
lbendlin
Super User
Super User

First thing you want to do is fix your data model.  Generally you do not want thefact table to influence the dimension tables.  Make all search arrows unidirectional, from the dimension to the fact.

 

Then put your provider column in a slicer, and let the data model do the work.

I tried switching the arrows to be unidirectional, but that did not solve the issue.

Being new (and probably naive), I liked the two-directional arrows so that options not allowed in the visuals do not appear in the slicers, like when I click an officve, onthe the employees who work at that offive appear in the slicers, ans such.

If you wouldn't mind looking over my schema, I include it below. To assit with parsing what is going on, in the below screen shot, I framed 3 structures that are generated dynamically from the structure in the upper left corner; these are the 3 roles involved in the process, requiring (I uderstand) their own tables.

I realize that there is a ton of nuance in schema design, but, if you have the time, please, help me pinpoint the issue, I would be greatly appreciative of your help. 

I can supply whatever other collateral data you may need, and answer whatever questions you may have, to help further this solution.

Thank you, Ibendlin.

Nothing attached but in general you want to follow the star/snowflake schema where dimension tables sit on the outside of the data model and point inwards to the fact table(s).  I understand your argument about the convenience of facts filtering slicer choices, but that is a bit of a trap and most of the time you will regret it later.

Okay and roger that.

I feel that these are wise words, the deep meaning of which will only become apparent after I make bigger mistakes.

I will meditate on this (along with research and learn).

Thank you, kindly.

I am going to look into everything you said and try to make i twork from my end. 

 

It may be obvious to you, but I learned Power BI and relational tables for this report, and very much appreciate you help. 

 

When it works, I'll award Solution, I promise.

 

Thank you, Ibendlin.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.