Dax start at first non blank value

[Anonymous]

Hi,

My data runs from the years 1960 to 2070 with many 0 values at either side but also some 0 values through the "middle" portion of my data. If I sum the values with a measure and put them in a table with the year it includes all of the blank values at either side. I can't filter to include values greater than 0 as this will remove the 0 values in the middle portion of my data.

I there away make my formula start calculating from the first non 0 value?

Thanks in advance.

[AlB · Accepted solution]

Hi @Anonymous 

You could check whether the cumulative sum so far is zero, and in that case return blank. Something like 

Measure = 
IF ( [CumulativeSum] <> 0, [YourMeasure] )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

One sample for your reference, please check the following steps as below.

1. Insert an index column in power query.

2. To create two calcualted columns.

Countrows = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[value]=0 && 'Table'[year]<=EARLIER('Table'[year])))

Tags = 
VAR ind = 'Table'[Index] + 1
VAR ca =
    CALCULATE (
        MAX ( 'Table'[Countrows] ),
        FILTER ( 'Table', 'Table'[Index] = ind )
    )
var indmin = CALCULATE(MIN('Table'[Index]),FILTER('Table','Table'[value]<>0))-1
RETURN
    IF ( 'Table'[value] = 0 && 'Table'[Countrows] <> ca || 'Table'[Index]=indmin, "ignore", "count" )

3. Then we can get the excepted reulst by filtering the Tags column.

For more details, please check the pbix as attached.

[Anonymous]

Hi @v-frfei-msft 

I was hoping you could help me with a follow on query to the one previously posted.

Let's say that the years in list you provided solution for relate to Field1. This solution works well for a single field scenario.

My data actually comprises numerous fields i.e. Field2, Field3, Field4 etc. Each field has years that can overlap and so when additional fields are added to the dataset the Tags column does not 'count' years that have a 0 value in the middle that field's associated data.

Is there a way to amend the formula so that it repeats the process each time it encounters a new field?

Thanks

[AlB · Accepted solution]

Hi @Anonymous 

You could check whether the cumulative sum so far is zero, and in that case return blank. Something like 

Measure = 
IF ( [CumulativeSum] <> 0, [YourMeasure] )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[jessicamewald]

As simple as this is, I was struggling with this for 3 days, then I came upon this post and it was exactly what I needed. Thank you!!!!!