Fill blank values

[nicolasvc]

I have a table that contains a column with integer values ​​and other empty ones (Blank), but I would like to be able to fill the empty values ​​with -1 when there is a value greater than 0 in a the product A, and 0 in case it is 0, such as the second table shows:

Store	Product	Value
1	A	1
1	B
1	C
2	A	2
2	B
2	C
3	A	0
3	B
3	C

Store	Product	Value
1	A	1
1	B	-1
1	C	-1
2	A	2
2	B	-1
2	C	-1
3	A	0
3	B	0
3	C	0

Is it possible to occupy a conditional within the EARLIER function, or is there another way to do it in DAX?

[v-zhangti · Accepted solution]

Hi, @nicolasvc 

You can try creating new calculated columns and measures to fill in blank values.

1. Calculated column

New value =
IF (
    [Value] <> BLANK (),
    [Value],
    IF (
        CALCULATE (
            MIN ( 'Table'[Value] ),
            FILTER ( 'Table', [Store] = EARLIER ( 'Table'[Store] ) && [Product] = "A" )
        ) > 0,
        -1,
        IF (
            CALCULATE (
                MIN ( 'Table'[Value] ),
                FILTER ( 'Table', [Store] = EARLIER ( 'Table'[Store] ) && [Product] = "A" )
            ) = 0,
            0
        )
    )
)

   2. Measure

Measure =
IF (
    MAX ( 'Table'[Value] ) <> BLANK (),
    MAX ( 'Table'[Value] ),
    IF (
        CALCULATE (
            MAX ( 'Table'[Value] ),
            FILTER ( ALL ( 'Table' ), [Store] = MAX ( 'Table'[Store] ) && [Product] = "A" )
        ) > 0,
        -1,
        IF (
            CALCULATE (
                MAX ( 'Table'[Value] ),
                FILTER ( ALL ( 'Table' ), [Store] = MAX ( 'Table'[Store] ) && [Product] = "A" )
            ) = 0,
            0
        )
    )
)

Best Regards,

Community Support Team _Charlotte

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

[Stachu]

Is Value a regular column/calculated column in the model table or a measure in the visual?
If it's a regular column then it cannot be modified with DAX, but it should be possible with M. Alternatively an additional calculated column can be added that will have the blanks filled.

If it's a calculated column/measure then what's its syntax?