Forum Discussion
suppress row that zero
If user click on A on pie chart, the table suppose to show on column A that have value more than 0. Currently it showing 0 value as well. How to show only value more than 0 for column A, in other words to suppress zero value of the selected data? pls advise. TQ
Hi Magesen - Create a DAX measure that filters based on the selection from the pie chart.
Dynamic_Column_Filter =
VAR SelectedCategory = SELECTEDVALUE('Table'[Category])
RETURN
SWITCH(
TRUE(),
SelectedCategory = "A", IF(SUM('Table'[A]) > 0, SUM('Table'[A]), BLANK()),
SelectedCategory = "B", IF(SUM('Table'[B]) > 0, SUM('Table'[B]), BLANK()),
SelectedCategory = "C", IF(SUM('Table'[C]) > 0, SUM('Table'[C]), BLANK()),
SelectedCategory = "D", IF(SUM('Table'[D]) > 0, SUM('Table'[D]), BLANK()),
BLANK()
)Remove the original columns (A, B, C, D) from the table visual and replace them with the new measure.Ensure that the interaction between the pie chart and the table visual is set correctly (the table should be filtered by the selection in the pie chart).
Now, when the user clicks on the pie chart:
If "A" is selected, the table will only show rows where column A has values greater than 0.
If "B" is selected, the table will show rows where column B has values greater than 0, and so on for other categories.
This approach dynamically adapts to the selected pie chart slice and displays only relevant data in the table, fulfilling your requirement.
Hope this works at your end.
3 Replies
Hi Magesen - you can achieve this by apply Filter Condition:
Drag the Column A field to the "Visual Level Filters".
In the filter settings for Column A, set the condition to greater than 0 (A>0). Preview: Now, when you click on segment "A" in the pie chart, the table should only show rows where the value of Column A is greater than 0.
Hope this helps.
rajendraongole1 the solution that u had provided is not working as per expected.
Based on your solution, if user click A on the pie chart, the table shows records that have A more than 0 but if user click B on pie chart, the table does not show any record bcoz the filter already applied to show A more than 0 only. please advise solution for if user click A on pie chart, the table to show A more than 0, if user click on B, table to show B more than 0, if user click on C, table to show C more than 0, if user click on D, table to show D more than 0.Hi Magesen - Create a DAX measure that filters based on the selection from the pie chart.
Dynamic_Column_Filter =
VAR SelectedCategory = SELECTEDVALUE('Table'[Category])
RETURN
SWITCH(
TRUE(),
SelectedCategory = "A", IF(SUM('Table'[A]) > 0, SUM('Table'[A]), BLANK()),
SelectedCategory = "B", IF(SUM('Table'[B]) > 0, SUM('Table'[B]), BLANK()),
SelectedCategory = "C", IF(SUM('Table'[C]) > 0, SUM('Table'[C]), BLANK()),
SelectedCategory = "D", IF(SUM('Table'[D]) > 0, SUM('Table'[D]), BLANK()),
BLANK()
)Remove the original columns (A, B, C, D) from the table visual and replace them with the new measure.Ensure that the interaction between the pie chart and the table visual is set correctly (the table should be filtered by the selection in the pie chart).
Now, when the user clicks on the pie chart:
If "A" is selected, the table will only show rows where column A has values greater than 0.
If "B" is selected, the table will show rows where column B has values greater than 0, and so on for other categories.
This approach dynamically adapts to the selected pie chart slice and displays only relevant data in the table, fulfilling your requirement.
Hope this works at your end.
