Remove 0 values

[Nandu_3033]

• 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. 

[Nandu_3033 · Accepted solution]

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

[Nandu_3033]

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
)

[mark_endicott]

@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. 

[Nandu_3033]

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 

[mark_endicott]

@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. 

[AhmadBakr]

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,

1

)

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,

[Ankur04]

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,