Forum Discussion
Table visual not filtering based on slicer
Hi,
I have a simple sales table with a measure to calculate much sold and the target goal.
I have a table visual that looks like this
| Sales Rep | Goal | % |
| Michael | 1000 | 54% |
| Jacob | 1200 | 41% |
| Billy | 800 | 50% |
| Robert | 1500 | 45% |
I have a slicer for the sales rep name. It is a separate calculated table DISCTINT(Sales Rep) that has a relationship to multiple tables so this slicer can filter on all connected tables. But when one is selected (example Billy) this is what happens to the table:
| Sales Rep | Goal | % |
| Michael | ||
| Jacob | ||
| Billy | 800 | 50% |
| Robert |
I would like to not see the other reps when Billy is selected. The "%" measure is causing it to not filter as expected. This is the measure I'm using:
SWITCH (
TRUE(),
[Goal] = 0 && [Sold] > 0, 1,
[Goal] > 0 && [Sold] = 0, 0,
[Goal] = 0 && [Sold] = 0, 0,
DIVIDE ( [Sold], [Goal], 0 )
)
Hi ,Your % measure is breaking the natural filtering context. it’s evaluating across all reps instead of respecting the slicer. That’s why when you select Billy, the table still shows the other reps with blank Goal/Sold values.
Measures in DAX always calculate in the current filter context.
You can do some workarounds-
Use IF( HASONEVALUE() )
% Achieved =
IF (
HASONEVALUE ( Sales[Sales Rep] ),
SWITCH (
TRUE(),
[Goal] = 0 && [Sold] > 0, 1,
[Goal] > 0 && [Sold] = 0, 0,
[Goal] = 0 && [Sold] = 0, 0,
DIVIDE ( [Sold], [Goal], 0 )
)
)
2. Simplify with Direct DivisionIf your Goal and Sold are already measures scoped per rep
% Achieved = DIVIDE ( [Sold], [Goal], 0 )
Then let the slicer do the filtering .no need for the SWITCH unless you want special handling for zero case.
3.Sometimes you need to explicitly bind the measure to the row-
% Achieved =
CALCULATE (
SWITCH (
TRUE(),
[Goal] = 0 && [Sold] > 0, 1,
[Goal] > 0 && [Sold] = 0, 0,
[Goal] = 0 && [Sold] = 0, 0,
DIVIDE ( [Sold], [Goal], 0 )
),
ALLEXCEPT ( Sales, Sales[Sales Rep] )
)If this helped, please mark it as a solutions and kudos would mean a lot! It encourages contributors and keeps the community strong.
Thank you
sunita
2 Replies
Hi ,Your % measure is breaking the natural filtering context. it’s evaluating across all reps instead of respecting the slicer. That’s why when you select Billy, the table still shows the other reps with blank Goal/Sold values.
Measures in DAX always calculate in the current filter context.
You can do some workarounds-
Use IF( HASONEVALUE() )
% Achieved =
IF (
HASONEVALUE ( Sales[Sales Rep] ),
SWITCH (
TRUE(),
[Goal] = 0 && [Sold] > 0, 1,
[Goal] > 0 && [Sold] = 0, 0,
[Goal] = 0 && [Sold] = 0, 0,
DIVIDE ( [Sold], [Goal], 0 )
)
)
2. Simplify with Direct DivisionIf your Goal and Sold are already measures scoped per rep
% Achieved = DIVIDE ( [Sold], [Goal], 0 )
Then let the slicer do the filtering .no need for the SWITCH unless you want special handling for zero case.
3.Sometimes you need to explicitly bind the measure to the row-
% Achieved =
CALCULATE (
SWITCH (
TRUE(),
[Goal] = 0 && [Sold] > 0, 1,
[Goal] > 0 && [Sold] = 0, 0,
[Goal] = 0 && [Sold] = 0, 0,
DIVIDE ( [Sold], [Goal], 0 )
),
ALLEXCEPT ( Sales, Sales[Sales Rep] )
)If this helped, please mark it as a solutions and kudos would mean a lot! It encourages contributors and keeps the community strong.
Thank you
sunita
This worked. Thanks.
