slicers

[ThomasDay]

Hello,  I'm new to Power Bi and have been doing my homework diligently to avoid asking ridiculous questions.  (Rob Collie's book and started book on M).

I'm stumped though on something I don't see discussed...and need guidance on approach.  I have a file of hospital provider  Files mapnumbers (top of chart).  There is one record per provider--6K. The related files contain millions of rows of public data associated with each provider.  Power bi handles these things beautifully--I can make all sorts of tables.  Success!

Now I'm trying to figure out how to add a cohorts capability using the cohorts table on the right above).  The idea is that each provider falls into a bunch of named categories. So for example it's bed size range (<100, 100-200 and so on), it's margin (<0, 1-10%), it's safety rating...an ever expanding list of analyzed categories.  Potentially hundreds.

So, take a look at the cohorts file--there are now three records for each provider.  All told they boil down to the list on the right.  I'd like to be able to create a way to select from a list of groups, the categories of providers to show.  The final table is simply a visual that shows an example with the providers reduced by all the filters.

The Groups and categories to picka distilled list of providers

My question is:  Do I just create hundreds of fields in my provider table and make filters from those OR can has someone created a solution with a "categories" file like I'm trying to figure out?

As you can see, I'm pretty lost--can you help or point me the right direction?

Tom

[nikil]

If I understand this right, you need will need a categories table which has a unique list of categories and another mapping table which lists the categories that a provider belongs. Basically 1:M relationship between Categories and Mapping table and 1:M relationship between Providers and Mapping table. This should solve your problem.

Thanks

Check out Chicagoland Power BI User Group

[ThomasDay]

OK, I will check that out.  I already have the 1:M between Providers and Mappings, and now I will create a M:1 between Mappings and Cohort Names and see how the slicer works etc.  Thank you...will report back!

Tom

[ThomasDay]

Hello again,

I changed things around as I understood the suggestion to create an intermediate mapping file.

Here's the mapping file--with 17 records--7 for the Tot IP Beds categories (like Tot IP Beds 500-750)--7 for the Tot Routine Bed categories, and 3 for the Hosp Type categories. Each provider is in each category SO...each Provider has 3 records.

The net result seems to be that when I put a slicer out there with the 17 Name_Group categories and select multiple Name_Groups--I get multiple provider records.  So, that's a bummer.

RelationshipsThe Match File

I've highlighted the relationship fields, show the match file, and finally the results for boston--selecting GenAcute and a few sizes--you see providers in multiple columns--one for each category they're in.

Any ideas?

I think I'll either want to create new columns in the main provider file--one for each cohort.  So eventually I'll add 100 columns to 5000 records.    OR perhaps I could add 100 tables and relate them to the main provider file--one for each cohort and each provider will only have one record in each.  What do you think?

Either way, the slicers will be AND slicers so the results will be one line for each provider that matches all of them.  Sound right?

Thanks up front for bearing with me on this.

Tom

Which of these two approaches seems best?