DAX formula to find first greater than 0 value in a column

[Anonymous]

Hi,

i want to find out a DAX formula to get below calculated column (that is the result column that i want)

logic is as below

there are rows with different Code numbers and one code is considerd as one block (that means calculations should apply for each code separetly)

1. find out the first greater than 0 Stock value for each code

2. copy that value and 0 values (which are in above rows) to calculated column

3. all other values below to that value(1) should equal to value in Quantity column 

i have showed the expected output in 'calculated' column. 

Highly appricate any input

thank you

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create two columns

min_rank = RANKX(FILTER(Sheet2,Sheet2[code]=EARLIER(Sheet2[code])),[stock],,ASC,Dense)

Column =
VAR value_rank1 =
    CALCULATE (
        SUM ( [stock] ),
        FILTER ( ALLEXCEPT ( Sheet2, Sheet2[code] ), Sheet2[min_rank] = 1 )
    )
RETURN
    IF (
        value_rank1 = 0,
        IF ( [min_rank] = 1 || [min_rank] = 2, [stock], [quantity] ),
        IF ( [min_rank] = 1, [stock], [quantity] )
    )

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-juanli-msft  thank you very much. it works perfectly