Forum Discussion
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 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 |
For fun only, a showcase of powerful Excel worksheet formula,
3 Replies
For fun only, a showcase of powerful Excel worksheet formula,
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
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
