Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a simple linechart with MonthName on the x-axis (this is derived from my own custom built calendar going from Apr to Mar as per Financial Year) and a DISTINCTCOUNT Measure of IDs for the Y axis, I would like to add a Trendline.
I do not see the option at all when using the Line Chart or Clustered Column chart:
Can anyone help? Thjis should be easy to do, its very frustrating!
Thanks
Solved! Go to Solution.
@ArchStanton
Maybe try creating a new measure. Now for the return part you may want to change yours to COUNTX or SUMX depending on what your wanting your results to be. I used AVERAGEX for my example. LINEST AND LINESTX are new to Power BI from their Power BI Desktop update in February I believe. They can be used to create a simple linear regression line or trend line.
Simple Linear Regression =
Thanks!
Apologies for the daft sounding question, where should I be writing this measure?
I have a customised Date Calendar called Date 2 which is linked to a Table called Cases (thats what I'm counting the no of text IDs of).
I'm trying to substitute your code with my data fields, can you help me with this?
Simple Linear Regression =
Hi Sorry about this, my DISTINCTCOUNT of IDs is derived from the 'Cases' table but I'm
struggling to substitute your Values e.g. Known [X] & [Y] etc with my actual table names
My Date table is called Date2.
Can you tell what belongs where please?
Much appreciated
@ArchStanton
Your Known[X] & [Y] become the new names of the fields. You don't need to substitute anything else. For example:
VAR Known =
FILTER(
SELECTCOLUMNS(
ALLSELECTED(Date2[Date]),
"Known[X]", Date2[Date]
"Known[Y]", [DISTINCT COUNT & SUM]
),
AND(
NOT(ISBLANK(Known[X])),
NOT(ISBLANK(Known[Y]))
)
)
VAR SlopeIntercept =
LINESTX(Known, Known[Y], Known[X])
VAR Slope =
SELECTCOLUMNS(SlopeIntercept, "Slope", [Slope1])
VAR Intercept =
SELECTCOLUMNS(SlopeIntercept, "Intercept", [Intercept])
RETURN
AVERAGEX(
DISTINCT(Date2[Date]
Intercept + Slope * Table[Date]
)
I can see Date in your example so I'm assuming that should be my Date2 calendar.
I'm struggling with the ALLSELECTED attributes, I've tried various combinations and nothing works
Thanks, i was.
Still not working though:
VAR Known =
FILTER(
SELECTCOLUMNS(
ALLSELECTED(Date2[Date]),
"Known[X]", Date2[Date],
"Known[Y]", 'Cases'[DISTINCT COUNT & SUM]
),
AND(
NOT(ISBLANK(Known[X])),
NOT(ISBLANK(Known[Y]))
)
)
VAR SlopeIntercept =
LINESTX(Known, Known[Y], Known[X])
VAR Slope =
SELECTCOLUMNS(SlopeIntercept, "Slope", [Slope1])
VAR Intercept =
SELECTCOLUMNS(SlopeIntercept, "Intercept", [Intercept])
RETURN
AVERAGEX(
DISTINCT('Date2[Date]),
Intercept + Slope * Table[Date]
))
I see it now. VAR Known is the name of your measure. Name your measure something else and move the VAR Known in front of the FILTER.
Slope1 and Intercept are underlined in Red (Cannot find name messages)
No need to worry about this.
You can use LINESTX or LINEST by themselves and create new tables. The table will create a single row with 10 columns. Two of those columns are Slope1 and Intercept.
Almost there, thanks for persevering with me on this!
Line 22 = DISTINCT('Date2[Date]),
Yes that fixed it, I should have spotted that myself.
However, I know get this, there is something up with Slop1 & Intercept as they are marked RED
Hi may I ask how did you fix that? cause i get the same error..
Are you talking about the red lines under [Slope1] and [Intercept]? If so, there is nothing to fix. You measure should still work without any issues. If you're getting an error when you try to create the measure then that is potentially a different issue.
yes i have the red line under Slope1 and Intercept,
but If i dont fix it its showing this error visual..
It's saying ...🤔
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |