Forum Discussion

MarDen94's avatar
MarDen94
Icon for Helper I rankHelper I
2 years ago
Solved

Summarize multiple columns in one slicer

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 FALS...
  • _elbpower's avatar
    _elbpower
    2 years ago

     

    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)

     

     

     

_elbpower's avatar
_elbpower
Icon for Resolver III rankResolver III
2 years ago

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:

  1. Create a new Calculation Group: In Power BI Desktop, go to the Modeling tab and click on "New Calculation Group."

  2. 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.

  3. 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.

  4. 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

  • MarDen94's avatar
    MarDen94
    Icon for Helper I rankHelper I
    2 years ago

    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!

  • MarDen94's avatar
    MarDen94
    Icon for Helper I rankHelper I
    2 years ago

    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: 

     

    Dataset, Filter & Measure

     

    CalcGroup

     

    Slicer options