Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bamba98
Helper I
Helper I

Return first non-blank value of field for min Period

I have the following example data:

bamba98_1-1594923720877.png

 

I want to add a column that should return the Value for a certain ID when it was first recorded as indicated by Period. I used the following Query to obtain the result: FirstValue=IF(CALCULATE(MIN(table[Period]),ALLEXCEPT(table,table[ID]))=table[Period], table[Value], BLANK())

 

This gave me the result as indicated below.

 

bamba98_0-1594923676818.png

 

The problem with these result are that the Query did not take into account that some Values are empty for the Period when it was first recorded and it therefore returns BLANK() fields for that certain ID and Period. What I want is to return the FirstValue for the first Period where Value is not blank. As the example below shows: 

bamba98_0-1594924335005.png

 

Any ideas on how to do this?

1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
Community Support

Hi @bamba98 ,

 

Please try this:

Column = 
VAR a =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            EARLIER ( 'Table'[Period] ) >= 'Table'[Period]
                && EARLIER ( 'Table'[ID] ) = 'Table'[ID]
                && 'Table'[Value] <> BLANK ()
        ),
        'Table'[Period]
    )
VAR b =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Period] = a
                && EARLIER ( 'Table'[ID] ) = 'Table'[ID]
        )
    )
RETURN
    IF ( 'Table'[Period] = a, b, BLANK () )

v-xuding-msft_0-1594963194700.png

 

 

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

View solution in original post

9 REPLIES 9
v-xuding-msft
Community Support
Community Support

Hi @bamba98 ,

 

Please try this:

Column = 
VAR a =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            EARLIER ( 'Table'[Period] ) >= 'Table'[Period]
                && EARLIER ( 'Table'[ID] ) = 'Table'[ID]
                && 'Table'[Value] <> BLANK ()
        ),
        'Table'[Period]
    )
VAR b =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Period] = a
                && EARLIER ( 'Table'[ID] ) = 'Table'[ID]
        )
    )
RETURN
    IF ( 'Table'[Period] = a, b, BLANK () )

v-xuding-msft_0-1594963194700.png

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@bamba98 , Try a new column like

calculate(firstnonbank([Value],blank()), filter(Table, [ID] = earlier([ID])))

Pragati11
Super User
Super User

Hi @bamba98 ,

 

Try something as below:

Pragati11_0-1594916010516.png

Modify the above DAX using your table name and column name.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

hi @Pragati11 and @amitchandak 

 

I don't see that you took the period into account. The dataset that I have is not ordered. 

 

Or am I missing something?

@bamba98 , Data you shared, is not making it clear.

Can you share sample data and sample output in table format? 

@amitchandak @Pragati11 

 

I have tried to improve my explanation. Let me know if it is still not clear.

@bamba98 , Try like

if([period]= calculate(firstnonbank([period],blank()), filter(Table, [ID] = earlier([ID]))),[Value],blank())

@Pragati11 It should take both ID and Period into account. My Query is wrong as it does not take into account that the field for value is empty for the mimimum period. It should therefore go to the next minimum poriod and return that value if the field for value is not empty. 

Hi @bamba98 ,

 

Do you want both ID and PERIOD to be taken into account or just ID or just PERIOD?

Because your query is not clear. I have shown the result you are trying to get based on your screenshot.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.