Help creating measure to filter data

[donodackal]

Hello

Is there a way I can filter the data from the below table based on how many locations a product is sold? For instance, I would like to filter the data visualised by:

• Products sold at only one location
• Products sold at two locations
• Product sold at three locations
• Products sold at all four locations.

Also, Is there a way I can add a filter to my dashboard where if I select two or more locations specifically, the data is filtered to products sold specifically to both or more locations selected (excludes products sold just to one of the selected locations)?

Table Name:	Product Sales
Product ID	No of Units	Total Value	Sale Date	Location
Product 1	10	500	15/07/2022	Location 1
Product 2	5	250	16/07/2022	Location 2
Product 3	2	100	17/07/2022	Location 1
Product 4	4	200	18/07/2022	Location 1
Product 1	2	100	19/07/2022	Location 2
Product 2	4	200	20/07/2022	Location 3
Product 3	3	150	21/07/2022	Location 2
Product 4	8	400	22/07/2022	Location 1
Product 1	7	350	23/07/2022	Location 3
Product 2	7	350	24/07/2022	Location 2
Product 3	4	200	25/07/2022	Location 3
Product 4	4	200	26/07/2022	Location 1
Product 1	7	350	23/07/2022	Location 4
Product 2	7	350	24/07/2022	Location 2
Product 3	4	200	25/07/2022	Location 3
Product 4	4	200	26/07/2022	Location 1

[Anonymous]

Hi @donodackal,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[CNENFRNL · Accepted solution]

For fun only, a showcase of powerful Excel worksheet formula,

[Jos_Woolley]

Hi,

First create a separate, single-column table to be used as a filter which comprises integers from one up to the maximum number of locations you would like to filter for (four, in your example). Assuming this table and its only column are both named 'Unique Location Count', create this measure:

MyMeasure =
VAR UniqueLocationCount =
    SELECTEDVALUE( 'Unique Location Count'[Unique Location Count] )
VAR MyTable =
    ADDCOLUMNS(
        SUMMARIZE( 'Product Sales', 'Product Sales'[Product ID] ),
        "Unique Locations",
            CALCULATE(
                DISTINCTCOUNT( 'Product Sales'[Location] ),
                ALLEXCEPT( 'Product Sales', 'Product Sales'[Product ID] )
            )
    )
RETURN
    SUMX( MyTable, 0 + ( [Unique Locations] = UniqueLocationCount ) )

which can then be dragged into the filters pane for your main visual and set equal to 1.

Of course, you could also dynamically derive the table 'Unique Location Count' from your dataset instead of creating it statically.

Regards