To group some data in the report, it is very common to create a custom column or a calculated column based on specific conditions, and then put this column in a slicer to filter visuals. This would generate more data in the model and the group result is fixed. If the data used in the conditions is from measures and needs to be changed by other filters in the report, measure solutions will be needed to provide dynamic results.
In this blog, I would like to show how to use measures to filter a table visual to make it display products in selected groups whichever groups you select.
I create a sample with product sales data like below. There are three tables involved. In ‘Table’, it has Date, Product and Amount columns. In ‘Product’ table, it only has ‘Product’ column that contains all unique product names. In ‘Date’ table, it has Date and Month columns. ‘Product’ and ‘Date’ tables are dimension tables that are connected to ‘Table’.
In the report, I would like to group products based on the monthly sales amount by using a slicer to select a month at a time. There are 4 groups:
• < 200 • >= 200 and < 350 • >= 350 and < 450 • >= 450
I want to have a slicer to display these groups. I put Product and Amount data in a table visual. I hope the slicer should let me select one or multiple groups to filter the table visual. Then the table visual should show all products in selected groups. Once I change the selection, the table should display different group members. If I select all groups, it should show all products. How to implement this?
As a slicer only accepts column fields, I create a table that has group names as below image. Put ‘Amount Group’ into a slicer.
Create a basic measure Sales Amount = SUM('Table'[Amount]). Results returned by this measure will be dynamically changed by other filters and slicers. In this report, I have a month slicer to affect it.
Then create a measure to concatenate all selected groups’ index values. The index values are number type and concatenated ascendingly.