Forum Discussion

MarDen94's avatar
MarDen94
Icon for Helper I rankHelper I
3 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
    3 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)

     

     

     

MarDen94's avatar
MarDen94
Icon for Helper I rankHelper I
3 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

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

Please share your sample PBI file

    • _elbpower's avatar
      _elbpower
      Icon for Resolver III rankResolver III
      3 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)

       

       

       

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

        Thanks, this was the solution I was looking for