Forum Discussion

nicolasvc's avatar
nicolasvc
Icon for Helper III rankHelper III
4 years ago
Solved

Fill blank values

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:

 

StoreProductValue
1A1
1B 
1C 
2A2
2B 
2C 
3A0
3B 
3C 

 

StoreProductValue
1A1
1B-1
1C-1
2A2
2B-1
2C-1
3A0
3B0
3C0

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

Need Help
Show and Tell
Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

2 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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?

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    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.