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
tonymaclaren
Helper I
Helper I

lastnonblank in measure

Hi

I have a problem I hope someone can help me with. I have a  measure that  calculates the trend values for some temperature analysis:

Estimated =
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'cetdata'[YEAR] ),
            "Known[X]", 'cetdata'[YEAR],
            "Known[Y]", [AVGTEMP]
        ),
        AND (
            NOT ( ISBLANK ( Known[X] ) ),
            NOT ( ISBLANK ( Known[Y] ) )
        )
    )
VAR Count_Items =
    COUNTROWS ( Known )
VAR Sum_X =
    SUMX ( Known, Known[X] )
VAR Sum_X2 =
    SUMX ( Known, Known[X] ^ 2 )
VAR Sum_Y =
    SUMX ( Known, Known[Y] )
VAR Sum_XY =
    SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
    AVERAGEX ( Known, Known[X] )
VAR Average_Y =
    AVERAGEX ( Known, Known[Y] )
VAR Slope =
    DIVIDE (
        Count_Items * Sum_XY - Sum_X * Sum_Y,
        Count_Items * Sum_X2 - Sum_X ^ 2
    )
VAR Intercept =
    Average_Y - Slope * Average_X
RETURN
    Intercept + Slope * SELECTEDVALUE ( 'cetdata'[YEAR] )

 

AVGTEMP = SUM(cetdata[AverageTemperature]) i.e. a measure

 

I need to be able to find the first and last values from Estimated so I can calculate the trend change for a given interval of years. I have no problem doing this with the 'cetdata'[YEAR] field using:

 

Last Trend = CALCULATE (
    SUM ( 'cetdata'[YEAR] ),
 LASTNONBLANK( cetdata[YEAR],'cetdata'[Estimated] ) ) 

 

but no luck with the Estimated measure. I am sure the solution is a FILTER, but I am stuck!. Any help would be very much appreciated.

 

Thanks

1 ACCEPTED SOLUTION

Thank you for your message.

The final solution which works perfectly and can be filtered by a slider for YEAR is is as follows:

Starting Temperature = 
VAR Estimate =
    SELECTCOLUMNS (
        KEEPFILTERS ( ALLSELECTED ( 'cetdata'[YEAR] ) ),
        "Estimate[X]", 'cetdata'[YEAR],
        "Estimate[Y]", [Estimated]
    )
VAR First =
    TOPN ( 1, Estimate, Estimate[X], ASC )
RETURN
    MINX ( First, Estimate[Y] )
Ending Temperature = 
VAR Estimate =
    SELECTCOLUMNS (
        KEEPFILTERS ( ALLSELECTED ( 'cetdata'[YEAR] ) ),
        "Estimate[X]", 'cetdata'[YEAR],
        "Estimate[Y]", [Estimated]
    )
VAR Last =
    TOPN ( 1, Estimate, Estimate[X], DESC )
RETURN
    MAXX ( Last, Estimate[Y] )
Temperature Difference = [Ending Temperature] - [Starting Temperature]

Tony

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

 

It becomes a lot easier to help if you share some data, explain the business problem and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

RCADate
Null1/2/2018
Null1/3/2018
Null1/4/2018
Null1/5/2018
Null1/6/2018
Too Much1/7/2018
Null1/8/2018
Null1/9/2018
Null1/10/2018
Too Many1/11/2018
Null1/12/2018
Null1/13/2018

Hi 

I have a problem, could you kindly help on this ?  I want to get the Nearest Non "Null " RCA.

For example, Today date is 1/13/2018, so what RCA i want to get is "Too many" , not "Too Much".

Could you help on this problem?

Hi,

 

In the RCA column, do you have blank cells or do you have a text entry NULL?  Also, can there be duplications in the Date column?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Dear Ashish

Thank you for your reply.Here is some sample data as requested. As I explained, I am trying to get the first and last non blank values from the Estimated column, which is a measure to calculate the regression trend in the data, as I reproduced in the DAX code in the precious post. So-- I want the value 8.87 for the year 1659 and the value 8.86 for the year 1688. Everything I try results in the value for AVGTEMP for the required years instead of the Estimated value, which I do not understand.

 

Tony

 

YEARAVGTEMPEstimated
16598.878.78
16609.18.78
16619.788.78
16629.528.79
16638.638.79
16649.348.79
16658.298.79
16669.868.8
16678.528.8
16689.518.8
16699.028.8
16708.968.81
16719.088.81
16728.828.81
16738.388.82
16748.128.82
16757.888.82
16768.848.82
16778.788.83
16788.458.83
16798.768.83
16808.898.83
16818.758.84
16829.058.84
16838.498.84
16847.958.84
16859.168.85
168610.158.85
16878.998.85
16887.868.86


@tonymaclaren wrote:

Dear Ashish

Thank you for your reply.Here is some sample data as requested. As I explained, I am trying to get the first and last non blank values from the Estimated column, which is a measure to calculate the regression trend in the data, as I reproduced in the DAX code in the precious post. So-- I want the value 8.87 for the year 1659 and the value 8.86 for the year 1688. Everything I try results in the value for AVGTEMP for the required years instead of the Estimated value, which I do not understand.

 

Tony

 

YEAR AVGTEMP Estimated
1659 8.87 8.78
1660 9.1 8.78
1661 9.78 8.78
1662 9.52 8.79
1663 8.63 8.79
1664 9.34 8.79
1665 8.29 8.79
1666 9.86 8.8
1667 8.52 8.8
1668 9.51 8.8
1669 9.02 8.8
1670 8.96 8.81
1671 9.08 8.81
1672 8.82 8.81
1673 8.38 8.82
1674 8.12 8.82
1675 7.88 8.82
1676 8.84 8.82
1677 8.78 8.83
1678 8.45 8.83
1679 8.76 8.83
1680 8.89 8.83
1681 8.75 8.84
1682 9.05 8.84
1683 8.49 8.84
1684 7.95 8.84
1685 9.16 8.85
1686 10.15 8.85
1687 8.99 8.85
1688 7.86 8.86

@tonymaclaren

Is Estimated a measure or a calculated column? I see it was mentioned as a measure while in this reply it is a column. If a calculated column, as per my understanding, I think you can get the expected with the DAX like 

Last Trend =
CALCULATE (
    AVERAGE ( 'cetdata'[Estimated] ),
    LASTNONBLANK ( cetdata[YEAR], "" )
)

If this is not your case, please post some raw data instead of the data in table visual(?) as showing above.

Hi Eric

Sorry for the confusion.Estimated is a measure (as documented in my first post, and LASTNONBLANK only accepts a column as a reference. Since my last post I have tested LASTNONBLANK on the data I posted and it works fine.The problem remains how do I get first and last values from the estimated measure, or any measure for that matter?The  Estimated measure is calculated on the fly in the filter context of a slicer (slider) which filters the YEARS. I should add it works perfectly in the visual.

 

For clarity here is the DAX for Estimated:

 

Estimated =
VAR Known =
    FILTER (
        SELECTCOLUMNS (
            ALLSELECTED ( 'cetdata'[YEAR] ),
            "Known[X]", 'cetdata'[YEAR],
            "Known[Y]", [AVGTEMP]
        ),
        AND (
            NOT ( ISBLANK ( Known[X] ) ),
            NOT ( ISBLANK ( Known[Y] ) )
        )
    )
VAR Count_Items =
    COUNTROWS ( Known )
VAR Sum_X =
    SUMX ( Known, Known[X] )
VAR Sum_X2 =
    SUMX ( Known, Known[X] ^ 2 )
VAR Sum_Y =
    SUMX ( Known, Known[Y] )
VAR Sum_XY =
    SUMX ( Known, Known[X] * Known[Y] )
VAR Average_X =
    AVERAGEX ( Known, Known[X] )
VAR Average_Y =
    AVERAGEX ( Known, Known[Y] )
VAR Slope =
    DIVIDE (
        Count_Items * Sum_XY - Sum_X * Sum_Y,
        Count_Items * Sum_X2 - Sum_X ^ 2
    )
VAR Intercept =
    Average_Y - Slope * Average_X
RETURN
    Intercept + Slope * SELECTEDVALUE ( 'cetdata'[YEAR] )

 

Tony 

Thank you for your message.

The final solution which works perfectly and can be filtered by a slider for YEAR is is as follows:

Starting Temperature = 
VAR Estimate =
    SELECTCOLUMNS (
        KEEPFILTERS ( ALLSELECTED ( 'cetdata'[YEAR] ) ),
        "Estimate[X]", 'cetdata'[YEAR],
        "Estimate[Y]", [Estimated]
    )
VAR First =
    TOPN ( 1, Estimate, Estimate[X], ASC )
RETURN
    MINX ( First, Estimate[Y] )
Ending Temperature = 
VAR Estimate =
    SELECTCOLUMNS (
        KEEPFILTERS ( ALLSELECTED ( 'cetdata'[YEAR] ) ),
        "Estimate[X]", 'cetdata'[YEAR],
        "Estimate[Y]", [Estimated]
    )
VAR Last =
    TOPN ( 1, Estimate, Estimate[X], DESC )
RETURN
    MAXX ( Last, Estimate[Y] )
Temperature Difference = [Ending Temperature] - [Starting Temperature]

Tony

Hi,

 

I do not understand.  Drag years to the visual and apply a condition with your choice of years.  Write the following measure

 

=SUM(Data[Estimated])

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.