Not all values showing in filter

[aashton]

Hello,

I hope to describe this the best I can, as I can't post a sample report.  I have a report of hospital locations.  I have page level filters on my report to filter out certain locations, say filters 1, 2 and 3.  There are 2 or 3 measures on the report that use ALLEXCEPT, to ignore these filters because I want to give total number of location regardless of what these filters are.   I now want to add another filter to the page, filter 4, and I DO want to filter these measures on this filter 4 and it is one of the fields in the ALLEXEPT.  Say filter 4 is, is this a pain location, Y or N?   The problem is, filters 1, 2 and 3 filter out any pain locations already, so when I add the filter to the page level filters, the only options are N,  and blank.  I need it to show Y so I can say all except Y and filter out the Pain locations out of these measures.  How do I get the page level filter to show all values?

[HotChilli]

I think you are telling us that you more-or-less have a 1 table model and the filters are limiting each other.  So you will need one or more dimension tables (containing the distinct values from a column) with 1:m relations to the Fact table.  Put the field from the dimension table in the filter.

[aashton]

It's two tables, Locations to Jobs, One to Many.  Filters 1, 2 and 3 are fields from Jobs, and the new filter I want to add is on Locations.  

Locations - ID, SiteName, Address, Pain, New/Existing, etc.

Jobs - Job ID, Job Name, Title, Location ID, etc.  

[v-yiruan-msft]

Hi @aashton ,

Based on your previous description, it appears that you have applied three page-level filters that will filter out some certain locations, while the fourth filter you are about to apply needs to show the pain location? (Y) of these filtered out locations, so that these filters are mutually exclusive. In order to better understand your problem and to make troubleshooting and give you a suitable solution, could you please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file(with fake data and simplified model). You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards