topic Re: Remove 0 values in DAX Commands and Tips

Remove 0 values

Nandu_3033 — Thu, 22 Aug 2024 06:42:39 GMT

I want to filter out rows where all the selected columns have a value of 0. If even one of the selected columns has a non-zero value, the row should be displayed. also for eg if i just remove the discount and discount non zero column. If the cost has any value that contains 0 it should filter out that too. similarily i will be adding 5 more columns. please let me know how resolve this issue. i am using field parameter for displaying the columns and rows in the table.

Re: Remove 0 values

Ankur04 — Thu, 22 Aug 2024 06:57:54 GMT

Hi,

Create a measure which will take sum of all the columns you are going to add. and use that measure in filter for your table visual and give the condition greater than 1.

Thanks,

Re: Remove 0 values

AhmadBakr — Thu, 22 Aug 2024 07:01:28 GMT

Hey Nandu_3033

What about adding a column with an if statement including all your conditional fields:

IF(

[Cost non Zero] + [Discount non-zero] <> 0,

)

Do not put a value for the false value of the condition so IF will return blank and it will not show in your report.

You can add in the IF condition as many measures/column names as you wish.

Only one important remark: I am assuming that the condition values cannot evaluate to negative numbers. If this is not the case, you can use the longer form of the condition:

[Cost non Zero] <>0 || [Discount non-zero] <> 0,

Re: Remove 0 values

mark_endicott — Thu, 22 Aug 2024 08:46:32 GMT

Nandu_3033 - Best way to manage this is to create a measure along the lines of:

SUMX( Table, Column1 + Column2 + Column3 + Column4 + Column5 )

Then add the measure to the "Visual level filters" and set to "Is not blank"

If this works for you, please accept as the solution, it helps with visibility for others when searching.

Re: Remove 0 values

Nandu_3033 — Fri, 23 Aug 2024 07:05:56 GMT

This code worked for my question but now the issue i am facing is that when i just take one column for eg Cost the values which conatins 0 its not getting removed. I tried adding the code for IsCostNonZero=1, and for others too inside the code given below then its not working at all.

DynamicShowRow_Final =
IF (
(

([IsCostNonZero] = 1 && [IsDiscountNonZero] = 1) ||
([IsCostNonZero] = 1 && [IsInvoicedAmtNonZero] = 1) ||
([IsDiscountNonZero] = 1 && [IsInvoicedAmtNonZero] = 1) ||
([IsNetSalesNonZero] = 1 && [IsInvoicedAmtNonZero] = 1) ||
([IsNetSalesNonZero] = 1 && [IsDiscountNonZero] = 1) ||
([IsCostNonZero] = 1 && [IsNetSalesNonZero] = 1) ||
([IsCostNonZero] = 1 && [IsDiscountNonZero] = 1 && [IsInvoicedAmtNonZero] = 1 && [IsNetSalesNonZero] = 1)
),
1,
0
)

Re: Remove 0 values

mark_endicott — Fri, 23 Aug 2024 09:30:13 GMT

Nandu_3033 - I see you have already ignored my suggestion.

Perhaps you could just try this by applying a filter to each column of the table, rather than through using DAX. It's really not necessary and it sounds like you are trying to over complicate it.

Re: Remove 0 values

Nandu_3033 — Mon, 26 Aug 2024 07:46:16 GMT

DynamicShowRow_Final =

SUMX(

'Sales Details',

[Cost] + [Discount] + [Invoiced Amount] + [Net Sales]

)

tried as you said didnt work for one column, also i cant apply for each column cuz i am using field parameters, i want to achieve this as well I want to filter out rows where all the selected columns have a value of 0. If even one of the selected columns has a non-zero value, the row should be displayed. also for eg if i just remove the discount and discount non zero column. If the cost has any value that contains 0 it should filter out that too.

approach on when choosed one column
Column 1
0 remove the whole row
1 show the whole row

for eg approach on two columns

Column 1 Column 2
0 0 filter out
0 1 show the row fully
1 0 show the row fully
similarily when adding other columns according to the needs hope you understand. please let me know if you could help me out

Re: Remove 0 values

Nandu_3033 — Tue, 27 Aug 2024 06:20:45 GMT

this is the answer
Visual_Filter =

VAR __SelectedValues =

SELECTCOLUMNS (

SUMMARIZE (

'Values',

'Values'[Parameter],

'Values'[Parameter Fields]

"Parameter", 'Values'[Parameter]

)

VAR NonZeroCheck =

IF(

SUMX(

__SelectedValues,

SWITCH(

[Parameter],

"Cost", IF([Cost] <> 0, 1, 0),

0 // Default if no match

)

) > 0, 1, 0

)

RETURN NonZeroCheck