Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
IuriLinoTi
New Member

Filtering Slicer vs Chart

Hi all.

I have two tables Prod[Talhao] 1-> * Talhao[Talhao] and i have two measures:

META = SUMX(Talhao,AVERAGE(Talhao[NN3_HECTAR])*25000)
COLHIDO% = SUM(Prod[QUANTIDADE])/[META]
 
This work perfectly when have no one filter, but when i filter in slicer, the measures dont work:
IuriLinoTi_0-1709120395904.png

 

IuriLinoTi_1-1709121286076.png

When filter in chart, the measure also work, but when filter in slicer, dont:

IuriLinoTi_3-1709121377083.png

 

1 ACCEPTED SOLUTION
123abc
Community Champion
Community Champion

It seems like you're encountering a common issue with Power BI when using slicers and filters. When slicers are applied, they filter the data differently from when filters are applied directly to visualizations. This can affect the results of your measures, as you've observed.

To ensure that your measures behave consistently whether you filter using slicers or filter panes directly on visualizations, you can adjust your DAX measures to consider the context correctly.

One approach is to use functions like CALCULATE and FILTER to modify the context within which your measures are evaluated. Here's how you can modify your measures META and COLHIDO%:

 

META =
VAR AvgHectare = AVERAGE(Talhao[NN3_HECTAR]) * 25000
RETURN
CALCULATE(
SUMX(Talhao, AvgHectare),
ALL(Talhao)
)

COLHIDO% =
VAR TotalQuantity = SUM(Prod[QUANTIDADE])
RETURN
DIVIDE(
TotalQuantity,
[META],
0
)

 

In these modified measures:

  • META: We use CALCULATE to override any filters applied by slicers by using the ALL function on the Talhao table. This ensures that the SUMX function operates on all rows of the Talhao table, regardless of any slicer selections.
  • COLHIDO%: We calculate the ratio of TotalQuantity to [META] based on the context established by the slicers.

With these modifications, your measures should behave consistently whether you filter using slicers or directly on charts. Adjustments might be necessary depending on your exact requirements and data model.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

View solution in original post

1 REPLY 1
123abc
Community Champion
Community Champion

It seems like you're encountering a common issue with Power BI when using slicers and filters. When slicers are applied, they filter the data differently from when filters are applied directly to visualizations. This can affect the results of your measures, as you've observed.

To ensure that your measures behave consistently whether you filter using slicers or filter panes directly on visualizations, you can adjust your DAX measures to consider the context correctly.

One approach is to use functions like CALCULATE and FILTER to modify the context within which your measures are evaluated. Here's how you can modify your measures META and COLHIDO%:

 

META =
VAR AvgHectare = AVERAGE(Talhao[NN3_HECTAR]) * 25000
RETURN
CALCULATE(
SUMX(Talhao, AvgHectare),
ALL(Talhao)
)

COLHIDO% =
VAR TotalQuantity = SUM(Prod[QUANTIDADE])
RETURN
DIVIDE(
TotalQuantity,
[META],
0
)

 

In these modified measures:

  • META: We use CALCULATE to override any filters applied by slicers by using the ALL function on the Talhao table. This ensures that the SUMX function operates on all rows of the Talhao table, regardless of any slicer selections.
  • COLHIDO%: We calculate the ratio of TotalQuantity to [META] based on the context established by the slicers.

With these modifications, your measures should behave consistently whether you filter using slicers or directly on charts. Adjustments might be necessary depending on your exact requirements and data model.

 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors