Group your data according to dynamic measure results

[v-henryk-mstf]

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.

Selected Groups =
CONCATENATEX (
    VALUES ( 'Amount Groups'[Index] ),
    'Amount Groups'[Index],
    ,
    'Amount Groups'[Index], ASC
)

Then create the following measure. Put this measure on the table visual to play as a visual-level filter and set it to show items when value is 1.

Amount Group Filter = 
VAR condition_1 = [Sales Amount] < 200
VAR condition_2 = [Sales Amount] >= 200 && [Sales Amount] < 350
VAR condition_3 = [Sales Amount] >= 350 && [Sales Amount] < 450
VAR condition_4 = [Sales Amount] >= 450
RETURN
    SWITCH (
        [Selected Groups],
        "1", IF ( condition_1, 1, 0 ),
        "2", IF ( condition_2, 1, 0 ),
        "3", IF ( condition_3, 1, 0 ),
        "4", IF ( condition_4, 1, 0 ),
        "12", IF ( condition_1 || condition_2, 1, 0 ),
        "13", IF ( condition_1 || condition_3, 1, 0 ),
        "14", IF ( condition_1 || condition_4, 1, 0 ),
        "23", IF ( condition_2 || condition_3, 1, 0 ),
        "24", IF ( condition_2 || condition_4, 1, 0 ),
        "34", IF ( condition_3 || condition_4, 1, 0 ),
        "123", IF ( condition_1 || condition_2 || condition_3, 1, 0 ),
        "124", IF ( condition_1 || condition_2 || condition_4, 1, 0 ),
        "134", IF ( condition_1 || condition_3 || condition_4, 1, 0 ),
        "234", IF ( condition_2 || condition_3 || condition_4, 1, 0 ),
        1
    )

Now, the table visual in the report only displays products in the selected month and selected groups.

This is a simple sample. You can customize the conditions per your need.

Author: Jing Zhang
Reviewer: Kerry Wang & Ula Huan