Forum Discussion

Nandu_3033's avatar
Nandu_3033
Regular Visitor
1 year 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 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's avatar
    Nandu_3033
    1 year 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






7 Replies

  • Nandu_3033's avatar
    Nandu_3033
    Regular Visitor
    1 year 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






  • Ankur04's avatar
    Ankur04
    Resolver II
    1 year ago

    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,

     

  • AhmadBakr's avatar
    AhmadBakr
    Helper IV
    1 year ago

    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,

     

  • mark_endicott's avatar
    mark_endicott
    Super User
    1 year ago

    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. 

  • Nandu_3033's avatar
    Nandu_3033
    Regular Visitor
    1 year 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
      1 year 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
        1 year 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