First non null value

[cz1750]

Hi,

I am looking to get the first non null value in power bi. Is there anyway to achieve this by not using sql

I.e from the following source data

Id. Value1 value2 Date
1 null null 1/2/18
1 abc null 1/1/18
1 def lll. 1/12/17

I would want the result to be returned in one row per id based on the latest date per non null field

For example the result for the data above would be

Id value1 value2
1 abc lll


Thanks

John

[v-ljerr-msft · Accepted solution]

Hi @cz1750,

Based on my test, you should be able to use the formula(DAX) below to create a new calculate table to get the expected result in your scenario. 

Table = 
SUMMARIZE (
    Table1,
    Table1[Id],
    "value1",
    VAR maxDate =
        CALCULATE (
            MAX ( Table1[Date] ),
            FILTER ( Table1, Table1[Value1] <> BLANK () )
        )
    RETURN
        CALCULATE ( MAX ( Table1[Value1] ), Table1[Date] = maxDate ),
    "value2",
    VAR maxDate =
        CALCULATE (
            MAX ( Table1[Date] ),
            FILTER ( Table1, Table1[Value2] <> BLANK () )
        )
    RETURN
        CALCULATE ( MAX ( Table1[Value2] ), Table1[Date] = maxDate )
)

Regards

[cz1750]

Thanks a million for your help. That worked perfectly

[cz1750]

Thanks a million for your help. That worked perfectly.