topic Re: Summarize multiple columns in one slicer in DAX Commands and Tips

Summarize multiple columns in one slicer

MarDen94 — Thu, 05 Oct 2023 11:41:21 GMT

Hi,

I've got a question regarding some DAX-logic to create a slicer.

I am working with a table similar to this one:

ID	Option1	Option2	Option3
1	TRUE	TRUE	FALSE
2	FALSE	TRUE	FALSE
3	TRUE	FALSE	TRUE
4	FALSE	FALSE	TRUE
5	TRUE	TRUE	TRUE

I am trying to figure out a way to create a slicer that enables me to check on of the options and show data for all lines that have these options. However I want to avoid creating 3 slicers, so the slicer should contain all 3 options as selectable.

Is there a way to do this for me using DAX?

NB. it would be possible to replace the boolean-fields with text where TRUE is replaced by Option# and FALSE is null.

Thanks in advance

Re: Summarize multiple columns in one slicer

_elbpower — Thu, 05 Oct 2023 17:00:15 GMT

You can achieve this using Calculation Groups in Power BI. Calculation Groups allow you to create dynamic slicers that can be used to switch between different calculations or filters in your report. In your case, you want to create a slicer that allows you to select one or more options (Option1, Option2, Option3) and filter the data accordingly.

Here's how you can set up a Calculation Group to achieve this:

Create a new Calculation Group: In Power BI Desktop, go to the Modeling tab and click on "New Calculation Group."
Define Calculation Items: In the Calculation Group editor, define calculation items for each option (Option1, Option2, Option3). Name these calculation items accordingly.
- For Option1:
  - Calculation Formula: FILTER('YourTable', 'YourTable'[Option1] = TRUE())
- For Option2:
  - Calculation Formula: FILTER('YourTable', 'YourTable'[Option2] = TRUE())
- For Option3:
  - Calculation Formula: FILTER('YourTable', 'YourTable'[Option3] = TRUE())
These formulas use the FILTER function to filter the table based on the selected option.
Add a Slicer to Your Report: Drag and drop the Calculation Group you created onto your report canvas. It will automatically create a slicer that allows you to select one or more options.
Customize the Slicer: You can customize the slicer to allow multiple selections by going to the Slicer settings and enabling the "Multi-select with OR logic" option. This will allow you to select multiple options, and the report will show data for any option that is selected.

Now, when you use the slicer, you can select one or more options (Option1, Option2, Option3), and the report will dynamically filter the data based on your selections. This avoids the need for multiple slicers and provides a more user-friendly experience.
Let me know if you have any questions

Re: Summarize multiple columns in one slicer

MarDen94 — Thu, 19 Oct 2023 08:41:13 GMT

Hi,

I saw that the calculation groups in the most recent version of PBI Desktop are included.

I downloaded this version and started with the steps you provided.

However in step 4 I cannot find the "Multi-select with OR logic" option you mention. Could you point me in the right direction for this?

Thanks again!

Re: Summarize multiple columns in one slicer

_elbpower — Fri, 20 Oct 2023 12:01:34 GMT

It should be part of slicer settings.

Re: Summarize multiple columns in one slicer

MarDen94 — Mon, 23 Oct 2023 09:30:29 GMT

Unfortunately I am still struggeling to make it completely work.

I've gotten to a point where I was able to create the calculation group and it works (to an extent).

But when I select multiple options the cout of IDs always shows the total number of possible IDs instead of a total filtered with OR-logic (current selection should return 7).

Hoefully the following screenshots help in explaining the issue:

Re: Summarize multiple columns in one slicer

_elbpower — Mon, 23 Oct 2023 09:44:24 GMT

Please share your sample PBI file

Re: Summarize multiple columns in one slicer

MarDen94 — Mon, 23 Oct 2023 09:56:45 GMT

Here you go: CalcGroupIssue.pbix

Re: Summarize multiple columns in one slicer

_elbpower — Mon, 23 Oct 2023 15:01:41 GMT

CountIds = VAR SelectedOptions = CONCATENATEX(VALUES('Calculation group'[Calculation group column]), 'Calculation group'[Calculation group column], ", ") VAR A = SUMMARIZE('Fact', 'Fact'[ID], "values", IF( (CONTAINSSTRING(SelectedOptions, "Option 1") && VALUES('Fact'[Option1]) = TRUE()) || (CONTAINSSTRING(SelectedOptions, "Option 2") && VALUES('Fact'[Option2]) = TRUE()) || (CONTAINSSTRING(SelectedOptions, "Option 3") && VALUES('Fact'[Option3]) = TRUE()), 1, 0 ) ) VAR DistinctIDs = SUMMARIZE(FILTER(A, [values] = 1), [ID]) RETURN COUNTROWS(DistinctIDs)

Re: Summarize multiple columns in one slicer

MarDen94 — Tue, 24 Oct 2023 07:52:03 GMT

Thanks, this was the solution I was looking for