Forum Discussion

paolomint's avatar
paolomint
Icon for Helper III rankHelper III
6 years ago
Solved

Remove rows (in visualization) with no values in several columns

Hi,

my table has a lot of zero in several columns:

 

 STOCK ITSTOCK FRSTOCK US
SHOES1000
SHIRT0140
HAT0022
GLASSES000
TIE33660
SOCKS0045
BELT000

 

How can I obtain the following result in visualization?

 

 STOCK ITSTOCK FRSTOCK US
SHOES1000
SHIRT0140
HAT0022
TIE33660
SOCKS0045

 

I need to keep only rows with at least one value on several columns.

thank you very much

Paolo

  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, paolomint 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a measure as below.

    Visual Control = 
var _result = 
CALCULATE(
    SUM('Table'[Value]),
    ALLEXCEPT('Table','Table'[Category])
)
return
IF(
    _result>0,
    1,0
)

     

    Then you need to put the measure in the visual level filter to get the result.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    6 years ago

    paolomint 

    NEW MEASURE = 

    IF (
      CALCULATE(
      [MEASURE],
      ALLSELECTED(TABLE[COLUMN])
     ) > 0 ,

    [MEASURE],
    BLANK()
    )

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube, LinkedIn

    • paolomint's avatar
      paolomint
      Icon for Helper III rankHelper III
      6 years ago

      thankyou Fowmy 

       

      unfortunatelly, I can not do this way,

      if I use the measure you proposed I will have the following result:

       

      STOCK ITSTOCK FRSTOCK US
      SHOES10  
      SHIRT 14 
      HAT  22
      TIE3366 
      SOCKS  45

       

      I need value zero when there is a value on at least one other column, not blank

      thank you

      paolo

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        6 years ago

        paolomint 

        Can you share the measure you used?

        ________________________

        Did I answer your question? Mark this post as a solution, this will help others!.

        Click on the Thumbs-Up icon if you like this reply 🙂

        YouTube, LinkedIn

  • Anand24's avatar
    Anand24
    Icon for Super User rankSuper User
    6 years ago

    Hi paolomint ,

     

    Why not create a simple zero check column with conditions and filter on that column.

     

    Refer below image:

     

    Give a thumbs up if this post helped you in any way and mark this post as solution if it solved your query !!!

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, paolomint 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a measure as below.

    Visual Control = 
var _result = 
CALCULATE(
    SUM('Table'[Value]),
    ALLEXCEPT('Table','Table'[Category])
)
return
IF(
    _result>0,
    1,0
)

     

    Then you need to put the measure in the visual level filter to get the result.

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.