Forum Discussion

Nandu_3033's avatar
Nandu_3033
Regular Visitor
2 years ago
Solved

Remove 0 values

  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 rem...
  • Nandu_3033's avatar
    Nandu_3033
    2 years ago
    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's avatar
Nandu_3033
Regular Visitor
2 years ago

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's avatar
mark_endicott
Super User
2 years ago

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's avatar
    Nandu_3033
    Regular Visitor
    2 years ago
    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