Filter for products across regions

DAX DAX Calculations
1

Hi

I need to filter products that exist in all of the selected regions

  1. find common products in the selected regions is the priority and
  2. can this be a flagging field that I can use in a slicer to switch and see only those products that are present in selected regions. For this I am looking to create a column that has values Yes, No.

Tried different things with ALLSELECTED and Countrows on the Products field. Not able to write a condition to check if each product exists across selected regions.

Any help is welcome. Thank you.

For example in the attached pbix -
if regions East and West are selected, the table should be filtered for B, D
if regions East and South are selected, the table should be filtered for A

Products across regions.pbix (18.7 KB)

2

Can someone please point me on how to approach this.

3

Hello @satichun,

You can add a simple DAX formula and add it to your Filter Visual

Count of Region = IF(COUNT('Table'[Region])>1, "1", "0")

image
image2074×467 52.4 KB

4

Thanks for looking Alex.

This would not work if I select more than 2 regions - for example if I select 3 regions, there can be 2 products across 2 regions but I need only those products that are across all 3 selected regions.

Imagine I have more regions such as NorthEast, SouthWest, SouthEast - then I could select 4 or 5 regions and need to see products present across all selected regions.

5

I adapted the code to only show the products in common. Is this good for you?

Count of Region = 
VAR _Products = 
CALCULATE (
        DISTINCTCOUNT ('Table'[Region] ))

VAR _CountRegion =     
CALCULATE (
        DISTINCTCOUNT ('Table'[Region] ),
        REMOVEFILTERS( 'Table'[Product] ))

RETURN IF( _Products= _CountRegion, "1", "0")
6

Hi @satichun, did the response provided by @alexbadiu help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

7

Hi @satichun, we’ve noticed that no response has been received from you since the 4th of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

8

This works good.

I ideally would have wanted to use the 1/0 in a slicer but this is good for me. Thank you for your help and time Alex.

1 Like