Forum Discussion
How to exclude an entry from visualisation table if both fields are showing zeros
Anonymous
Thanks for the sample data. It makes things much easier.
Seeing the dataset, the data contains rows which, when grouped by the IDs, sum out to 0, This means that filter expressions referenced to row value <> 0 wil still return these rows (they have values but their aggregated sum is 0). Here is an example:
If you want to exclude these rows since they sum to 0, use the following measure instead to filter the visual in the filter pane (set the value to "is greater or equal to 1")
Exclude Sum to 0 rows = VAR Summ = //This creates a virtual table summarizing the measures by the IDs/fields included SUMMARIZE ( Table1, Table1[Project ID], Table1[Sub-Project ID], Table1[Chart of Accounts], Table1[Project Manager ID], Table1[Line Type], "Fixed", [Sum Fixed Costs], "Variable", [Sum Variable Cost] ) RETURN //The measure filters the virtual table to count rows containing "ACTUAL" and with summarized values which are not 0 COUNTROWS ( FILTER ( Summ, Table1[Line Type] = "ACTUAL" && OR ( [Fixed] <> 0, [Variable] <> 0 ) ) )and you will get this result
Anonymous
Updated the measure logic.
ShowHide =
VAR _variableCost = [Variable Cost]
VAR _fixedCost = [Fixed Cost]
VAR _result =
IF (
AND (
_variableCost = 0
|| ISBLANK ( _variableCost ),
_fixedCost = 0
|| ISBLANK ( _fixedCost )
),
"hide",
"show"
)
RETURN
_resultNandu Krishna
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 👍
Proud to be a Super User!
Hi, the updated measure above doesn't work. I am attaching the raw data here in case you are able to figure out what field is causing the measure to not work.
P/S I only want to show "ACTUAL" (as per Pivot).
Based on your raw data, I have created a calculated column.
ShowHideColumn = IF(AND('Table'[Fixed Cost]=0,'Table'[Variable Cost]=0),"Hide","Show")Then applied it in the visual level filter. Please refer to the attached pbix file.