Calculation Group Filters

[lherbert501]

Hi there,

I'm wondering if its possible to achieve the below scenario. 

I have a field parameter that slicer toggles on 2 calculation groups. I have 4 calc items in calc group 1 but I want to display 3 of them in the graph and the full 4 in the matrix, working off the same slicer.

The problem I'm having, is if I do a simple visual level filter to exclude calc item 4 on the graph, then the graph returns blank when I toggle onto calc group 2.

Is there a way around this to use the same slicer but exclude 1 calc item from the graph just for that calc group?

Thanks 

[DataNinja777 · Accepted solution]

Hi @lherbert501 ,

The problem you're running into is because a visual-level filter is static; it always tries to apply the rule you set. When you switch to "Calc Group 2," the filter is still trying to exclude "Calc item 4," but since none of the items from your second group match the filter's "allowed" values (Calc Items 1, 2, or 3), the visual has no data to show and goes blank.

The way around this is to use a dynamic DAX measure for your graph instead of the filter pane. This measure will contain the filtering logic itself. By returning a BLANK() value for the specific item you want to hide, you can effectively remove it from the graph, as Power BI visuals don't plot blank results.

You can use the following DAX formula to create a new measure.

Graph Measure = 
IF(
    SELECTEDVALUE('Calc Group 1'[Name]) = "Calc item 4",
    BLANK(),
    [Your Base Measure]
)

This DAX code works by first checking if the current calculation item being evaluated is "Calc item 4" from your first calculation group. If it is, the formula returns BLANK(). If the item is anything else from "Calc Group 1," or, crucially, any item from "Calc Group 2," the condition is false, and the formula proceeds to calculate your original base measure. This dynamic check is why the graph won't go blank when you switch slicer selections.

To put this into practice, create a new measure in your model and paste in the DAX code, making sure to replace 'Calc Group 1'[Name], "Calc item 4", and [Your Base Measure] with your specific table names, column names, and the original measure you are using. After creating it, select your graph visual and drag this new [Graph Measure] into the 'Y-axis' or 'Values' field, replacing the original measure. Your matrix visual should be left as is, continuing to use [Your Base Measure]. This will result in the matrix showing all four items while the graph dynamically shows only three when "Calc Group 1" is selected, and both visuals working correctly for "Calc Group 2."

Best regards,

[johnt75 · Accepted solution]

You need to create a third calculation group, with a higher precedence than the existing 2, so that it can intercept the other calculation groups before they are applied.

The new calculation group needs a single calculation item like

Calculation item = IF( SELECTEDVALUE('Calculation group 1'[Ordinal]) <> 3, SELECTEDMEASURE() )

where 3 is the ordinal of the calculation item you want to exclude from the matrix visual. You can see what the ordinal is by looking in the table view at the calculation group you want to affect. If all ordinals are the same, which they are by default, select the calculation group in the modelling view and reorder the items. You can simply drag the calc item you want to exclude up to the top and then put it back where it should be, that will give each item its own ordinal.

Finally, apply the new calcultion item as a filter on the chart. The chart will then show only the items you want, while the matrix will still show everything.

See attached PBIX for a working example.

[johnt75 · Accepted solution]

You need to create a third calculation group, with a higher precedence than the existing 2, so that it can intercept the other calculation groups before they are applied.

The new calculation group needs a single calculation item like

Calculation item = IF( SELECTEDVALUE('Calculation group 1'[Ordinal]) <> 3, SELECTEDMEASURE() )

where 3 is the ordinal of the calculation item you want to exclude from the matrix visual. You can see what the ordinal is by looking in the table view at the calculation group you want to affect. If all ordinals are the same, which they are by default, select the calculation group in the modelling view and reorder the items. You can simply drag the calc item you want to exclude up to the top and then put it back where it should be, that will give each item its own ordinal.

Finally, apply the new calcultion item as a filter on the chart. The chart will then show only the items you want, while the matrix will still show everything.

See attached PBIX for a working example.

[lherbert501]

@johnt75  this worked great for the one exclusion but when I went to do the same to the second calc group toggled on the slicer it doesnt like it. Is there a way around this?

[v-dineshya · Accepted solution]

Hi @lherbert501 ,

Thank you for reaching out to the Microsoft Community Forum.

1. If I wanted to do this for both calc groups, would the filter calc group have to have two seperate filters in it, or would I need a seperate item/group?

You do not need two separate calc groups. You can handle both inside a single filter calc group with just one calculation item.

Use ISSELECTEDMEASURENAME() or SELECTEDVALUE() against both groups, and return BLANK() when the unwanted item shows up. Please check below sample measure.

Filter Calc Item =
VAR CurrentItem1 = SELECTEDVALUE('Calc Group 1'[Name])
VAR CurrentItem2 = SELECTEDVALUE('Calc Group 2'[Name])
RETURN
SWITCH(
TRUE(),
CurrentItem1 = "Calc item 4", BLANK(),
CurrentItem2 = "Some item to exclude", BLANK(),
SELECTEDMEASURE()
)

Note: If you are on Calc Group 1, only “Calc item 4” is hidden in the chart. If you are on Calc Group 2, you can selective blank whichever items you want. Otherwise, it passes through the original measure. This filter group must be applied only on the chart. Leave the matrix using the original groups.

2. I went to do the same to the second calc group toggled on the slicer it doesnt like it. Is there a way around this?

Yes, the issue comes from how field parameters interact with calc groups. When you toggle between the two groups, only one of them is “active” at a time, so your filter group logic must account for that. If your current DAX only checks SELECTEDVALUE('Calc Group 1'[Name]), it will break when you switch to Calc Group 2, since that column has no row context. 

Now wrap the check in a condition that ensures the column exists in context. Please refer below example.

Filter Calc Item =
VAR Item1 = IF( ISCROSSFILTERED('Calc Group 1'[Name]), SELECTEDVALUE('Calc Group 1'[Name]) )
VAR Item2 = IF( ISCROSSFILTERED('Calc Group 2'[Name]), SELECTEDVALUE('Calc Group 2'[Name]) )
RETURN
SWITCH(
TRUE(),
Item1 = "Calc item 4", BLANK(),
Item2 = "Calc item X", BLANK(),
SELECTEDMEASURE()
)

ISCROSSFILTERED or HASONEVALUE referencing a column from the inactive group. This allows the same filter calc group to adapt dynamically depending on which calc group the parameter slicer has chosen.

Note: Use one extra calc group, but write the filter logic to handle both calc groups dynamically. Always apply this only to the chart, not the matrix. The blanking logic should explicitly check which group is active before filtering.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[lherbert501]

Amazing - Thanks @v-dineshya. Always learning 😀

[v-dineshya]

Hi @lherbert501 ,

 I hope that issue is resolved. Please do let us know if you have any further queries.

Regards,

Dinesh

[lherbert501]

Hi @johnt75 

Just to add to this. If I wanted to do this for both calc groups, would the filter calc group have to have two seperate filters in it, or would I need a seperate item/group?

Thanks

Liam

[lherbert501]

Thankyou @johnt75  & @DataNinja777  for taking the time. These are great solutions and work perfect.

[DataNinja777 · Accepted solution]

Hi @lherbert501 ,

The problem you're running into is because a visual-level filter is static; it always tries to apply the rule you set. When you switch to "Calc Group 2," the filter is still trying to exclude "Calc item 4," but since none of the items from your second group match the filter's "allowed" values (Calc Items 1, 2, or 3), the visual has no data to show and goes blank.

The way around this is to use a dynamic DAX measure for your graph instead of the filter pane. This measure will contain the filtering logic itself. By returning a BLANK() value for the specific item you want to hide, you can effectively remove it from the graph, as Power BI visuals don't plot blank results.

You can use the following DAX formula to create a new measure.

Graph Measure = 
IF(
    SELECTEDVALUE('Calc Group 1'[Name]) = "Calc item 4",
    BLANK(),
    [Your Base Measure]
)

This DAX code works by first checking if the current calculation item being evaluated is "Calc item 4" from your first calculation group. If it is, the formula returns BLANK(). If the item is anything else from "Calc Group 1," or, crucially, any item from "Calc Group 2," the condition is false, and the formula proceeds to calculate your original base measure. This dynamic check is why the graph won't go blank when you switch slicer selections.

To put this into practice, create a new measure in your model and paste in the DAX code, making sure to replace 'Calc Group 1'[Name], "Calc item 4", and [Your Base Measure] with your specific table names, column names, and the original measure you are using. After creating it, select your graph visual and drag this new [Graph Measure] into the 'Y-axis' or 'Values' field, replacing the original measure. Your matrix visual should be left as is, continuing to use [Your Base Measure]. This will result in the matrix showing all four items while the graph dynamically shows only three when "Calc Group 1" is selected, and both visuals working correctly for "Calc Group 2."

Best regards,