Forum Discussion
Filtering calculation group by slicer
- 2 years ago
Hi Oggie
For this type of requirement, one method I would suggest is:
- Set up a table similar to your filter table, but where the 2nd column contains Calculation Item names.
- 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:
- An existing Calculation Group 'Time Intelligence'.
- A table 'Time Intelligence Group' which relates Time Intelligence Groups (Report 1-3) to Calculation Items.
- 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 ResultThen 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
Hi Oggie
For this type of requirement, one method I would suggest is:
- Set up a table similar to your filter table, but where the 2nd column contains Calculation Item names.
- 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:
- An existing Calculation Group 'Time Intelligence'.
- A table 'Time Intelligence Group' which relates Time Intelligence Groups (Report 1-3) to Calculation Items.
- 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
- Oggie2 years agoFrequent Visitor
Worked like a charm! Thanks a million! 😀