How to create a table using dynamic columns chosen from a slicer to power a visual? - PowerBI

Question

I am trying to show summarized/aggregated data visually for products that have similar values for multiple user selected columns.

An example of the data (actual data has approximately 300 unique columns for entire product catalog):

Essentially I am trying to get to an end result resembling this visual:

Where you can select the columns via the slicer on the left and see summarized data for products grouped by values from the selected columns.

I tried to make a summarized table using dynamic columns from the slicer like so:

Using the following DAX:

SUMMARIZE (
    'ProductData',
    'ProductData'[fuel_type_label_id],
    'ProductData'[configuration_label_id],
    "Values",
        CONCATENATE (
            'ProductData'[fuel_type_label_id],
            " + " & 'ProductData'[configuration_label_id]
        ),
    "Total Items", COUNT ( 'ProductData'[item_id] ),
    "Total Revenue", SUM ( 'ProductData'[Revenue] )
)

However, I could not figure out how to make the GroupBy Column 1 & 2 dynamic to the slicer in the SUMMARIZE function, which I'm not even sure is possible.

Is it possible to pass the SUMMARIZE function dynamic columns from a slicer or is there a better way to accomplish what I am trying to do?

Answer 1

The SUMMARIZE function doesn't provide a native way to dynamically choose the columns based on a slicer or another user input. However, you can create a more dynamic behavior like below :

Create a measure that will return the desired value based on the selected slicer option. You can use the SWITCH function to return different results based on a slicer value.

Then use this measures inside the SUMMARIZE function to dynamically change the grouping and aggregation based on the slicer's selection.

GroupByColumn1 = 
SWITCH (
    SELECTEDVALUE( 'SlicerTable'[GroupByOption1] ),
    "Option1", 'ProductData'[fuel_type_label_id],
    "Option2", 'ProductData'[configuration_label_id]
    // Add more options as needed
)

GroupByColumn2 = 
SWITCH (
    SELECTEDVALUE( 'SlicerTable'[GroupByOption2] ),
    "Option1", 'ProductData'[fuel_type_label_id],
    "Option2", 'ProductData'[configuration_label_id]
    // Add more options as needed
)

DynamicSummarize = 
SUMMARIZE (
    'ProductData',
    [GroupByColumn1],
    [GroupByColumn2],
    "Values",
        CONCATENATE (
            [GroupByColumn1],
            " + " & [GroupByColumn2]
        ),
    "Total Items", COUNT ( 'ProductData'[item_id] ),
    "Total Revenue", SUM ( 'ProductData'[Revenue] )
)