Forum Discussion

Oggie's avatar
Oggie
Frequent Visitor
2 years ago
Solved

Filtering calculation group by slicer

Hi! 

 

I have setup a slicer solution which allows me to change the data in numerous cards from one of the following:

 

The layout of the filter table is as follows:

 

I would like to use the same logic to show the corresponding measures from the following calculation group in a table and a graph.

 

 

For example, the "MTD vs Budget"-selection should display the "MTD Act", "MTD Bu" and "MTD vs Bu" in a graph, and a "MTD vs PY"-selection should display the "MTD Act", "MTD PY" and "MTD vs PY". At the moment the graphs are manually filtered but it would be nice to connect it all together.

I tried to setup a mapping table as suggested in the following thread but since I want to have for example "MTD Act" included in 2 selections it doesn't work.
Solved: Filtering calculation group items - Microsoft Fabric Community

Is it possible to do another workaround? Or can I setup different Calculation groups that I can filter between in i Slicer? Please help! 🙂

Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi Oggie 

    For this type of requirement, one method I would suggest is:

    1. Set up a table similar to your filter table, but where the 2nd column contains Calculation Item names.
    2. Create a second Calculation Group with a Calculation Item that "filters" the original Calculation Items by blanking out the measure unless it is one of the filtered Calculation Items in the table in step 1.

    To give an example, the attached PBIX has:

    1. An existing Calculation Group 'Time Intelligence'.
    2. A table 'Time Intelligence Group' which relates Time Intelligence Groups (Report 1-3) to Calculation Items.
    3. A new Calculation Group 'Time Intelligence Filter' that blanks out unwanted Calculation Items.

    'Time Intelligence Group' table

     

    'Time Intelligence Filter' calculation group contains this Calculation Item:

    -- This calculation items returns SELECTEDMEASURE ()
-- only if Time Calc is within the list of calculation items in the column
-- 'Time Intelligence Group'[Time Intelligence Calculation Item]
VAR CurrentTimeCalc =
    SELECTEDVALUE ( 'Time Intelligence'[Time Calc] )
VAR TimeIntelligenceCalcItemFilter =
    VALUES ( 'Time Intelligence Group'[Time Intelligence Calculation Item] )
VAR Result =
    IF (
        CurrentTimeCalc IN TimeIntelligenceCalcItemFilter,
        SELECTEDMEASURE ()
    )
RETURN
    Result

    Then the above Calculation Item should be applied as a filter to any required visuals:

     

    Then the report works like this:

    There are certainly other possible approaches that use some method to filter the calculation items, but this seems to work quite well.

     

    Does something like this work for you?

     

    Regards

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    Hi Oggie 

    For this type of requirement, one method I would suggest is:

    1. Set up a table similar to your filter table, but where the 2nd column contains Calculation Item names.
    2. Create a second Calculation Group with a Calculation Item that "filters" the original Calculation Items by blanking out the measure unless it is one of the filtered Calculation Items in the table in step 1.

    To give an example, the attached PBIX has:

    1. An existing Calculation Group 'Time Intelligence'.
    2. A table 'Time Intelligence Group' which relates Time Intelligence Groups (Report 1-3) to Calculation Items.
    3. A new Calculation Group 'Time Intelligence Filter' that blanks out unwanted Calculation Items.

    'Time Intelligence Group' table

     

    'Time Intelligence Filter' calculation group contains this Calculation Item:

    -- This calculation items returns SELECTEDMEASURE ()
-- only if Time Calc is within the list of calculation items in the column
-- 'Time Intelligence Group'[Time Intelligence Calculation Item]
VAR CurrentTimeCalc =
    SELECTEDVALUE ( 'Time Intelligence'[Time Calc] )
VAR TimeIntelligenceCalcItemFilter =
    VALUES ( 'Time Intelligence Group'[Time Intelligence Calculation Item] )
VAR Result =
    IF (
        CurrentTimeCalc IN TimeIntelligenceCalcItemFilter,
        SELECTEDMEASURE ()
    )
RETURN
    Result

    Then the above Calculation Item should be applied as a filter to any required visuals:

     

    Then the report works like this:

    There are certainly other possible approaches that use some method to filter the calculation items, but this seems to work quite well.

     

    Does something like this work for you?

     

    Regards

    • Oggie's avatar
      Oggie
      Frequent Visitor
      2 years ago

      Worked like a charm! Thanks a million! ğŸ˜€