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

[paolomint]

Hi,

my table has a lot of zero in several columns:

	STOCK IT	STOCK FR	STOCK US
SHOES	10	0	0
SHIRT	0	14	0
HAT	0	0	22
GLASSES	0	0	0
TIE	33	66	0
SOCKS	0	0	45
BELT	0	0	0

How can I obtain the following result in visualization?

	STOCK IT	STOCK FR	STOCK US
SHOES	10	0	0
SHIRT	0	14	0
HAT	0	0	22
TIE	33	66	0
SOCKS	0	0	45

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

thank you very much

Paolo

[v-alq-msft · Accepted solution]

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.

[Anand24]

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 !!!

[Anand24]

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

[Fowmy]

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

thankyou @Fowmy 

unfortunatelly, I can not do this way,

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

STOCK IT	STOCK FR	STOCK US
SHOES	10
SHIRT		14
HAT			22
TIE	33	66
SOCKS			45

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

thank you

paolo

[Fowmy]

@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

[paolomint]

@Fowmy 

all the measure are like this:

(CALCULATE(sum('BUDGET CA'[BdgCR]),'MODELLO CE'[CatId1]=1) ) +0

I add "+0" to avoid blank value.

but doing this way, I need to manage the rows with only zero values.

If I do this way 

CALCULATE(sum('BUDGET CA'[BdgCR]),'MODELLO CE'[CatId1]=1)

I have several blank values, and it is not suitable for my aim.

STOCK IT	STOCK FR	STOCK US
SHOES	10
SHIRT		14
HAT			22
TIE	33	66
SOCKS			45

Thanks for your attention

[pranit828]

Hi @paolomint 

I would create a measure and add all the column in it.

Sum_Measure = [Col1] + [col2] + [Col3] + ...

And create a filter in the filter pane to display when the Sum_Measure is greater than 0.

[Fowmy]

@paolomint

This is your Existing Measure, You can replace your calculation

Existing Measure = SUM(DATA[VALUE])+0

Create a new measure which you will uuse in the matrix:

New Measure = 

IF (
    CALCULATE (
        [Existing Measure],
        ALLSELECTED (DATA[COUNTRY]))> 0,
        [Existing 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