A filter challenge...

[Anonymous]

Hi all, I have a filter challenge. So...the situation:

I have TABLE_1 which contains a column REGISTER_SIZE - this shows how many people are registered at each location (with each location having it's own unique LEGAL_ID).

TABLE_2 then contains those same LEGAL_ID locations, with monthly sales volume of specific PRODUCT_NAME. 

The front end of my report is setup to make it as simple as possible for users and so has a few fields in the filter pane which apply to all pages (to ensure the user has a consistent view as they go through the report). 

When I pull through the REGISTER_SIZE for an area (which is a combination of multiple individual LEGAL_ID locations) without a PRODUCT_NAME filter on, it gives me the right answer. However when I apply a filter to PRODUCT_NAME, because not all locations have sold that product, the report does as you would expect and filters REGISTER_SIZE down to just the sum of those who have sold that product. However I don't want it to. 

Is there a way to stop a specific filter (PRODUCT_NAME) that sits in the filter pane in applies to all pages from acting on any value contained within TABLE_1? 

[mussaenda]

Allexcept can help you.

You can provide a sample data and your desired output to help you better.

[Anonymous]

Looks just what I need. How do I apply DAX to a standard multirow card visual?

[v-frfei-msft]

Hi @Anonymous ,

Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Anonymous]

Hi @v-frfei-msft , thanks - a sample file can be found here - any bright ideas would be appreciated! Thanks. 

---

[JosefPrakljacic · Accepted solution]

You need to write a Measure like these one here

ListSizeMeasure :=
CALCULATE ( SUM ( LIST_SIZE[PRACTICE_LIST_SIZE] ), ALL ( sales ) )

The other problem you still need to resolve are your relationships. Try to achieve a 1:* Relationship - I can't help you here because I don't know your data.

Here you have a working example file. As you can see I'm filtering with the Geograpthy table and the sales part is not intervening.

If this post was helpful please kudo it and mark it as the solution.

Have a nice day Lewdow!

BR,

Josef

[JosefPrakljacic]

Hello lewdow,

if I understand you right you just need either the function all or allexcept. 

The answer to your question lies in this wunderful article from the SQLBI guys. -> https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

I hope the answer is helpful - if not please provide us with some sample data 🙂

BR,

Josef