cancel
Showing results for 
Search instead for 
Did you mean: 
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.

View solution in original post

amitchandak
Super User IV
Super User IV

@bamba98 , Try a new column like

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Pragati11
Super User III
Super User III

Hi @bamba98 ,

 

Try something as below:

Pragati11_0-1594916010516.png

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

 

Thanks,

Pragati


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


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? 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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())



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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


My Blog: Data Vibe
Connect: LinkedIn
Connect: Twitter






If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors