Forum Discussion

donodackal's avatar
donodackal
Helper I
4 years ago
Solved

Help creating measure to filter data

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 on...
Need Help
Jos_Woolley's avatar
Jos_Woolley
Solution Sage
4 years ago

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