Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
Hi,
It becomes a lot easier to help if you share some data, explain the business problem and show the expected result.
RCA | Date |
Null | 1/2/2018 |
Null | 1/3/2018 |
Null | 1/4/2018 |
Null | 1/5/2018 |
Null | 1/6/2018 |
Too Much | 1/7/2018 |
Null | 1/8/2018 |
Null | 1/9/2018 |
Null | 1/10/2018 |
Too Many | 1/11/2018 |
Null | 1/12/2018 |
Null | 1/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?
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 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
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |