Data Modeling (Relationship issue)

[Brent_LaCorte]

I have a data set that I am trying to filter a specific way and am unable to figure out how to set up the relationships to make it work. In this example I have 3 tables. 

The data table

Trans #	Associate	Shop	Amount
1234	Joe	Joe's #1	$1234
1235	Bill	Bill's #2	$12

The heirarchy table

Associate	Supervisor	Manager
Joe	Jim	John
Jerry	Jim	John
Bill	Bob	John

The shop table

Shop	Supervisor	Address
Bill's #1	Bill	123
Bill's #2	Bill	124
Joe's #1	Joe	125
Joes #2	Joe	126

I am trying to filter down to the adjuster level data by shop, however, I only want to see the shops that the supervisor is assigned to via a slicer or some other method. I currently am able to filter by manager, supervisor, or associate with no problem but as soon as I try to introduce the shop assignment it starts to give me the overall data for every single value and not the specific data that I am looking for. 

The heirarchy table is currently connected to the data set via the associate. I have tried connecting the shop table to the data set via the shop and then via the supervisor, or only to the supervisor, or only to the shop -- but no combination seems to work. 

Any help on this would be greatly appreciated.

[Kumail]

Hello @Brent_LaCorte 

If you could send sample .pbix that demonstrate what you are looking to get. It would really help providing you a quick solution.

Regards
Kumail Raza

[Brent_LaCorte]

Hey there @Kumail,

I have created a sample file but I for the life of me can't figure out how to attach it to this  post.

[Kumail]

Hello @Brent_LaCorte 

You can add the file to your drive and add the url to drive here.

I hope it helps.

Regards

Kumail Raza

[Brent_LaCorte]

@Kumail 

Let's see if this works for you. 

https://www.dropbox.com/s/rw14hfivxybtosw/Example.pbix?dl=0 

This is a very basic example of what I am working with. I can't upload the actual file due to privacy issues. I am also creating this in Excel through Power Pivot but it is essentially the same thing in Power BI. 

As you can see in the example when you click on Bill's shops that he is assigned to it shows ALL the shops where the associate had a transaction, including Craig's. I am looking to only show the values for the shops that are assigned to each supervisor. Again, this is very basic. The problem is excaberated with the larger dataset as there are 150+ shops split between 8 or so supervisors. 

[Kumail]

Hello @Brent_LaCorte 

Is this fine now

The .pbix file is given below for your quick reference. 

https://drive.google.com/file/d/16UHr3-dPrMM21woHpHiZjg0F81m6qxTx/view?usp=sharing

I hope this helps.

Regards

Kumail Raza

[Brent_LaCorte]

Good morning @Kumail 

I appreciate the quick response! This would absolutely normally work. However, the data sets that I am working with normally don't have the Supervisor attached to them. It's normally just the associate and the shop.

[Kumail]

Hello @Brent_LaCorte 

Its the calculated column added to the data table to get the Associate and Shop relationship working in the model. 

In the original data, you can also add this column in using the following dax function and create an inactive relationship, in the data model.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and I would love to see your like.

Regards

Kumail Raza

[Brent_LaCorte]

@Kumail I apologize, there are a lot of things I am trying to solve with this one fix and I thought I brought them all up but I just noticed something else. 

When filtering by Supervisor with the related function, it does indeed only show the results where they are the assigned supervisor but it also shows associates who are not on their team. In this instance Craig's results show transactions from Bill's team as well. I am trying to keep everything isolated to only showing results where that specific supervisior team had an impact.  I am trying to make this go vertical as well, meaning Manager, Director, Region (if need be). I hope that made sense.

[Brent_LaCorte]

@Kumail I see! You forced Power BI to add the column there. I was able to replicate in Excel through the data model and that does indeed work. 

A caveat though. If I wanted a broader solution... for instance the heirarchy goes up to Manager and Director levels. Would I then have to create the same functions and add their names to the data set that way? Is there another way to make it flow that I only need to use the heirarchy for the splicers or will I need to create multiple functions for the multiple data sets that I am analyzing?

[Kumail]

Hello @Brent_LaCorte 

It is because Joe and Jim have transaction data from Shop # 3 and Shop # 4 .

If you remove incorrect data from data table, things would look more relevant.

Hope this helps.

Regards

Kumail Raza

[Brent_LaCorte]

@Kumail 

The data itself isn't incorrect as sometimes Jim and Joe may have a transaction at those shops but they aren't on Craig's team so the numbers shouldn't be applied to Craig. The data is still relevant at a higher level, Manager for example, but looking specifically at the Supervisor team if the associate isn't on that supervisors team it needs to be filtered out and I am trying to do that without having a million slicers.

[Kumail]

As you requested initially, it is getting filtered by shop dimension through Supervisor filter. 

Thats getting a bit complicated..

You can create a measure that filters data by Hierarchy dimension with supervisor category where you select Shop dimension with supervisor category in filter. 

measure = 

CALCULATE(SUM(data[Amount]), RELATEDTABLE(shop_list))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and I would love to see your like.

Regards

Kumail Raza