Conflicting filter context for visual and for data inside a table visual

[Daxon101]

Hello Power BI community, thanks in advance for any assistance you can give.

So I have a report. In the report are three sets of filters that each filter one of three dimension tables. The first set of filters filteres a Product Dimension Table with a primary key EAN column and attribute columns containing text about "Brand", "Vareity", "Content" and "Pack Size".

It looks something like this.

EAN	Brand	Variety	Content	Pack Size
1234567891	Brown	Large	20L	20
1234567892	Brown	Small	2L	20
1234567893	Red	Small	2L	10

The second set of filteres filter a query containing information related to stores. The query is called Geography Dimension with a primary key "Store ID" and attributes like "Postcode", "Banner", "Territory". 

It looks something like this:

Store ID	Postcode	Banner	Territory
1232	1000	BuyHere	NorthWest1
1233	1100	BuyHere	NorthWest1
B234	1040	BestBuy	SouthEast2

The final filter filters a query relating to people. This one is called People Dimension Table and has a primary key column called Territory (this links to general geography query). 

It looks something like this:

Sales Rep	Sales Manager	Territory
Philip Robbin	James John	NorthWest1
Philip Robbin	James John	NorthWest1
Peter Parker	James John	SouthEast2
Tim Berners	Claire Voyance	SouthWest4

These dimension tables all relate to one data table called "Data table" with information about sales. The foreign keys are: Store ID and EAN. The data is in two columns as price sold at and units sold (making turnover if you multiply the two together). 

It looks like this:

Store ID	EAN	Price	Units sold
1232	1234567897	5	6
1233	1234567895	2	1
B234	1234567896	10	2

There are three relationships are as follows:

Data table has a *-1 (many to one) relationship with Geography Dimension. Bidirectional filtering and functions as expected in other parts of my report. 

Data table has a *-1 relationship with Product Dimension. Bidirectional filtering and functions as expected in other parts of my report. 

People Dimenison has a 1-* relationship with Geographical Dimension. Also bidirectional filtering and functions as expected in other parts of my report. 

Okay, so now that I've described the situation, I need to explain my issue a little more: Essentially, I would like to know which stores are selling which product (as in distribution of the product) and more importantly, which ones aren't and display this information in a useful way, essentially in a table. So the aim is to something like: 

1. Create a distinct count of all store IDs in the data set as these shops have sold a product at some point in the dataset.
2. Create a distinct count of all store IDs in the data set with the EAN filter context (as this would only select the shops that have sold that product in the data set). 
3. Compare the two to get distribution ratios and counts, as well a table that shows whether a particular store ID has distribution of that particular product or not.

HOWEVER, I have two problems:
1. I need to keep both tables in the geography filter context. This means that when I filter by postcode (in geography dimension table) or by sales rep (in the people dimension query - as it is linked to the geography table), it should display only those stores that fit in the filter context. 
2. When I add in sales rep, I get the unexpected functionality that the shops with distribution display correctly, but the ones without get their corresponding rows multiplied by the number of sales reps. 

My measures are below and I'll try to explain what I wanted to acheive with each:

// This measure attempts to get a count of all the stores in the dataset, while ignoring all the filter contexts. Needs to be adapted to only ignore the Product Dimension filter context, not the geography or people one. I've not managed so far, but I don't trust my understanding of things like allexcept and stuff to be doing it right.

1. Total open stores =

    CALCULATE(

        DISTINCTCOUNT('Data Table'[STORE ID]),

        ALL('Data Table')

    )

// This measure aims to grab all the store IDs in both the product and geography filter contexts. It seems to do this correctly, but I don't know if there is a better way to achieve this in the context of the other measures

2. Distribution count = DISTINCTCOUNT('Data Table'[STORE ID])

// This measure finds the ratio between the stores counted within the filter context and the stores outside the filter context (although as above, would need to be adapted to take geography context into account)

3. Distribution Ratio = DIVIDE([Distribution count], [Total Open Stores])

// Finally this measure just assigns a one or a zero depending on whether the store is "distributing" the product or not.

4. Has Distribution = IF([Distribution count] > 0, 1, 0)

The set up is relatively simple, but I'm not sure how to get the additional functionality to work. I know I need to add the geography and people filter context to that first measure somehow, but I'm not sure how. 

As for the displaying issues in the table, essentially what i get is a situation like this:

Store ID	Store Name	Has Distribution	M1
1234	HelloStore	1	Neo
1235	WorldStore	1	Morpheus
1236	BigStore	1	Trinity
1237	LittleStore	0	Cypher
1237	LittleStore	0	Agent Smith
1237	LittleStore	0	Apoc
1237	LittleStore	0	The Oracle

So basically 1237 (LittleStore) doens't have distribution and then the rows are miltiplied by the number of sales rep remaining in the geography filter context.

I hope I explained the problem adequately, let me know if there is anything else I can add to make this clearer, and again thanks in advance for any help you can give!

Appreciate it! Philip

[WinterMist]

@Daxon101 

Just realizing you won't want to show a Sales Rep Name for the total.

You can handle it like this.

Regards,

Nathan

[WinterMist]

@Daxon101 

My apologies for the delay.

NOTE: Don't ever try to have the community admin try to change your email address on your MS Fabric account.  I've been locked out of my @WinterMist account for over 2 weeks.  Just got back in yesterday.

What a headache.

Anyway, here is the solution for you now.

Just create the following 2 measures & pull them into your table visual:

NOTE: You don't really need [05 Territory ID].  I just added that for testing to show the test path from:

StoreID -> Territory ID -> Sales Rep.

So you can just remove it.  All you really need is [06 Sales Rep].   

(Sorry I incorrectly named it as Sales Rep "ID" in the screenshots below.)

Hopefully this is helpful to you.

Regards,

Nathan

[WinterMist]

@Daxon101 

1) Russo & Ferrari highly advise against the use of bidirectional relationships.  These should only be used in very rare cases, as they often cause unexpected & undesirable results within Power BI reports.  Additionally, it appears that all of the model relationships are bidirectional.  The model (at first glance) doesn't seem very complex.  Is there a compelling reason for the use of bidirectional relationships?  If not, please consider changing all relationships to standard SINGLE direction.

2) You state: "People Dimenison has a 1-* relationship with Geographical Dimension".  However, when looking at these 2 dimensions, the only column in common I see available for joining is the [Territory] column.  But [Territory] is not unique in either dimension.  So I am confused how this can be a 1 to MANY relationship.  It would have to be MANY to MANY, right?  Or perhaps you are joining on another column in these 2 dimensions that does not appear in the screenshots?

Based on the screenshots, this is the model I've created so far, which is looking very undesirable due to the above 2 reasons.

3) For the first of your 2 problems (Need to keep BOTH tables in Geography FC), you mention that you are filtering by Postcode or by Sales Rep.  How are you filtering your visuals?  Is this happening by slicers?  Would it be possible to share a screenshot of the visuals with which you are interacting so that I can see an example of how it is NOT displaying the correct stores for the FC?

4) For your second problem (the rows without distribution getting multiplied by number of Sales Reps), would it be possible to get a bit more test data that matches?  For example, your screenshot of results shows as follows:

- Store IDs (1234, 1235 & 1237) don't exist in the rest of the model for the screenshots provided.  Would you be able to fill them in for me?

- Originally, I thought your "M1" column referred to your Measure 1 for [Total Open Stores].  However, this doesn't make sense as the measure calculates a number, whereas your "M1" has strings in it ("Neo", "Morpheus", "Trinity" etc.).  So I'm confused what "M1" is.  Is that another data column from the model?  If so, could you also fill in a bit more of the model to account for these values?

5) For your first measure [Total Open Stores], you wrote "needs to be adapted to only ignore the Product Dimension".  To do this, just modify the ALL clause to refer to the Product Dimension instead of the Data Table.

If this doesn't work as intended, please show the data, the visual & the desired result.

Still studying remaining points...

Regards,

Nathan

[Daxon101]

Hi Nathan,

Thank you so much for this response - I'm blown away by how thorough it is - I was trying to be thorough myself when typing this problem up but I did let a couple of things slip throught the gaps, so apologies for any confusion that caused.

I'll try to address your points one by one:
1. The use of bidirectional filters really helps speed up the use of the report. There are many hundreds of products with many hundreds of stores. Being able to have the filters only display EANs or Stores of note to the sales reps seemed to increase the usability when I was talking to the reps. I'd love to keep them if possible, but if needs must then we can change. 

2. Your absolutely right that TERRITORY is the foreign and primary key pair in the People and Georgraphy dimension relationship. However, I did make the mistake of leave one duplicate. The relationship is 1-*. The data I provide is simulated to protect the names/location/brands involved (hence the use of matrix characters as some of the Sales Reps) which is also the answer to point 4 (for the sake of organisation I'll address it there)

3. I am filtering my visuals via slicers. I would be happy to connect to this information but I don't think I can share a screenshot publicly. 

4. So this is because I'm giving simulated data. To be clear, all the Stores IDs exist in the real model and the relationships seem to work as expected (except for my problem above). Please consider as corrections to my example tables:

Data table (made it so that the tables fit together properly)

Store ID	Store Name	Has Distribution	Sales Rep
1232	HelloStore	1	Philip Robbins
1233	WorldStore	1	Philip Robbins
B234	BigStore	1	Peter Parker
1237	LittleStore	0	Cypher
1237	LittleStore	0	Agent Smith
1237	LittleStore	0	Apoc
1237	LittleStore	0	The Oracle

People Dimension (my mistake for leaving in the duplicate row - this should fix the *-* issue)

Sales Rep	Sales Manager	Territory
Philip Robbin	James John	NorthWest1
Philip Robbin	James John	NorthWest1
Peter Parker	James John	SouthEast2
Tim Berners	Claire Voyance	SouthWest4

I'd be more comfortable sharing the report directly to you if that would help flesh out any further questions you have about the nature of the data.

5. Thanks for this, I can't believe I missed that one. Works as expected. Thank you.

Please let me know what you think of this, as a beginner I really appreciate the support. 

Best, Philip