Map not affected by slicer

[rvcedd]

Hello there,

I have a very simple data model with 2 tables and a 1 to many relationships. One of the tables contains Lat/Long values - the other contains a grouping category based on the ID.

The issue is that selecting an item from the "Category" filter using ChicletSlicer or the built-in Slicer does not trigger an update of values in the map. (All of the values are still displayed). The strange thing is that by clicking on a location on the map, it filters the category so this means the relationship works.

Does anyone know what is going on? I have double checked the "Edit interactions sections" and everything is set to respond.

PowerBI Dashboard here: https://1drv.ms/u/s!Avv4DxzuMuq2kIFAh4B_rA3wx-bUqg?e=kK7b0V

Screenshots below:

[Anonymous]

Hi, you are joining tables 'Grouping' and 'StopPoints_2018' based on the ID fields in both tables, however there are comparably few matches between the values in each of the ID fields. This might be the issue.

[rvcedd]

The number of matches should not affect the overall behaviour - this would be classed as a bug. Even if there was a single category in one of the tables and it only matched 1 entry in the other table this should still work. The fact that clicking on the map filters the category demonstrates that this is working.

In any case, I have updated the data model and only included a small subset of the data. The same behaviour exists.

Updated dashboard, same problem: https://1drv.ms/u/s!Avv4DxzuMuq2kIFBzqEVwHB2mn49mQ?e=HasYEo

[Anonymous]

The relationship between the 2 tables is such that the Grouping table does not filter the StopPoints table. You will need to modify that relationship.

Hope this helps,

Nathan

[rvcedd]

Hello Nathan - can you explain exactly what I need to modify? Currently the relationship is 1 to many - why is this not working and what is the correct solution?

[Anonymous]

The quickest way to address it would be to set the filter direction to both:

However, that's not considered a best practice. Another possibility is to create a third table. Then, you'd have your new Category dimension table (a distinct list of Categories), your Location dimension table (stays the same), and then a "Factless Fact Table" (stays the same). Each dimension table would filter the fact table in a one-many relationship.

Hope this helps,

Nathan

[rvcedd]

Hi Natel. Many thanks for your reply - I would like to implement the second suggestion that you made but I am not sure I follow through exactly what you are suggesting.

Currently I have 2 tables and you are recommedning adding a 3rd one. Would you be able to help me understand the structure of the final data model? It would perhpas be easier to simply do in it in powerbi and then send the file over but if you prefer text then that could work.

So currently I have 2 tables:

StopPoints_2018 with ID, Lat and Long

Grouping with ID and Category

You are proposing adding a 3rd table? what values would be in that table? Can you give me a working example in Power BI?

[Anonymous · Accepted solution]

1. Create a new Calculated Table:

Category = DISTINCT('Grouping'[Category])

2. Create a new Calculated Column in your grouping table:

Category Count = 1

3. Add a relationship between the new Category table and the Grouping table.

4. Add the Category Count to the map (size?)

5. Change the Slicer to the new Category table.

Here is the pbix.

Cheers,

Nathan

[rvcedd]

Ok - I see what you there - that helps a lot thank you so much. I still find it difficult to understand what is the reason behind having to add the Category Count to map - it seems counter intuitive and in no way helpful. I will mark the solution as an answer, thanks.

[Anonymous]

@rvcedd - The reason for the Category Count is to have a data point in the fact table. That is then used to filter to only the values that have data points. In other words, if there is no data in the fact table, the data point is excluded.