Slicer issue: Select all <> Individual selecting all values

[wtdpon]

Hi everyone,

I have an issue with a filter that I think may be caused by a relationship, and I am trying to figure out how to fix it without changing the relationship (if possible).

I have a
- 'dim subregion' table with : subregion id, name, country, manager id. 

- 'dim manager' table with: manager id, name, subregion id. (Some manager names are empty)

- 'fact sales' table with: subregion id and sales data.

The relationships are subregion.subregionid -> sales.subregionid and subregion.managerid <-> manager.managerid.

My issue is the following:

On my report I have several filters in the filter pane. When I filter by a subregion, my managers slicer gets filtered by the available managers in that subregion, this is expected and wanted. However, when I now filter with the managers slicer my data is not consistent. If I click on 'select all' it's showing 400K sales but if I look individually at each manager and add them up is adding to 380K. I think the issue may be with the blank values in managers, because there are blanks in all subregions.

E.g.

Subregion A with 'select all' managers (default) : 400801

Subregion A blank manager: 9000

Subregion A manager A 125000

Subregion A manager B 145000

Subregion A manager C: 101000

Is there a way to work around this?

[Adescrit]

Hi @wtdpon ,

Do you have a manager id in the sub-region table that does not exist in the dimManagers table? If so this could explain the difference (especially if the manager Id that exists in one table but not the other has a value related to it of around 2k).

[wtdpon]

Hi again. 

You were completely right, this is the issue.

Is there a workaround to solve this that does not requiere adding the missing ids to the file? (I'm thinking a flag measure/column and extra filters or something like that). I am going to send the missing ids to the team that manages the file, but it may take a while for them to fix it...

[Adescrit · Accepted solution]

Hi @wtdpon 

Personally I would be reluctant to build in any sort of workaround - in my opinion the only solution is to fix the underlying issues in the source data. 

The behaviour of Power BI is what I would expect. The total shows the total value from the column, but the value against the individual records only displays the manager names that are available. Keeping this view in your report would allow you to pick up any future data quality issues.

[wtdpon]

Thanks a lot again for your help!

[wtdpon]

Hi @Adescrit thanks for your reply!
I am going to check but this is a real possibility, since managers is mantained with a manual process. I'll check and reply again!