Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ArchStanton
Post Prodigy
Post Prodigy

Trendline needed

‎03-14-2023 09:44 AM

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: 

ArchStanton_0-1678812151748.png

 

Can anyone help? Thjis should be easy to do, its very frustrating!

 

Thanks

Labels:
Message 1 of 33
2,595 Views
0
1 ACCEPTED SOLUTION
nleuck_101
Resolver III
Resolver III
In response to ArchStanton

‎03-15-2023 10:12 AM

@ArchStanton 

I would think they would be the same line.

nleuck_101_0-1678900338557.png

 

View solution in original post

Message 17 of 33
2,453 Views
32 REPLIES 32
nleuck_101
Resolver III
Resolver III

‎03-14-2023 11:10 AM

@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 =

VAR Known =
    FILTER(
        SELECTCOLUMNS(
            ALLSELECTED(Table[Date]),
            "Known[X]", Table[Date],
            "Known[Y]", Table[DistinctCount measure]
        ),
        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(Table[Date]),
        Intercept + Slope * Table[Date]
    )
Message 3 of 33
1,902 Views
0
ArchStanton
Post Prodigy
Post Prodigy
In response to nleuck_101

‎03-15-2023 02:41 AM

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 =

VAR Known =
    FILTER(
        SELECTCOLUMNS(
            ALLSELECTED(Date2[Date]),
            "Known[X]", Date2[Date],
            "Known[Y]", Cases[DistinctCount measure]
        ),
        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(Table[Date]),
        Intercept + Slope * Table[Date]
    )
Message 4 of 33
1,892 Views
0
nleuck_101
Resolver III
Resolver III
In response to ArchStanton

‎03-15-2023 05:17 AM

@ArchStanton 

The measure should be in your Cases table.

Message 5 of 33
1,888 Views
0
ArchStanton
Post Prodigy
Post Prodigy
In response to nleuck_101

‎03-15-2023 06:56 AM

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

Message 6 of 33
1,883 Views
0
nleuck_101
Resolver III
Resolver III
In response to ArchStanton

‎03-15-2023 07:11 AM

@ArchStanton 
Your Known[X] & [Y] become the new names of the fields. You don't need to substitute anything else. For example:

VAR SlopeIntercept =
    LINESTX(Known, Known[Y], Known[X])
You do not need to replace Known[Y] or Known[X] with anything. You've already identified them earlier in the VAR Known.
Does that make sense? Are you receiving an error?
Message 7 of 33
1,874 Views
0
ArchStanton
Post Prodigy
Post Prodigy
In response to nleuck_101

‎03-15-2023 07:41 AM 
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]
                    )
Message 33 of 33
1,865 Views
0
ArchStanton
Post Prodigy
Post Prodigy
In response to nleuck_101

‎03-15-2023 07:41 AM

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

 

ArchStanton_0-1678890982084.png

 

Message 8 of 33
1,870 Views
0
nleuck_101
Resolver III
Resolver III
In response to ArchStanton

‎03-15-2023 07:45 AM

@ArchStanton 
You are missing a comma at the end

nleuck_101_0-1678891533157.png

 

Message 9 of 33
1,866 Views
0
ArchStanton
Post Prodigy
Post Prodigy
In response to nleuck_101

‎03-15-2023 07:55 AM

Thanks, i was.

 

Still not working though:

ArchStanton_0-1678892067070.png

 

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]
                        ))
Message 10 of 33
1,859 Views
0
nleuck_101
Resolver III
Resolver III
In response to ArchStanton

‎03-15-2023 08:07 AM

@ArchStanton 

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.

nleuck_101_0-1678892831191.png

 

Message 11 of 33
1,854 Views
0
ArchStanton
Post Prodigy
Post Prodigy
In response to nleuck_101

‎03-15-2023 08:22 AM

Slope1 and Intercept are underlined in Red (Cannot find name messages)

Message 31 of 33
1,835 Views
0
nleuck_101
Resolver III
Resolver III
In response to ArchStanton

‎03-15-2023 09:30 AM

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.

Message 32 of 33
1,825 Views
0
ArchStanton
Post Prodigy
Post Prodigy
In response to nleuck_101

‎03-15-2023 08:19 AM

Almost there, thanks for persevering with me on this!

 

ArchStanton_1-1678893519469.png

 

Line 22 = DISTINCT('Date2[Date]),

 

Trendline =
    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 * Date2[Date]
                        ))))))
 
 
Message 12 of 33
1,840 Views
0
nleuck_101
Resolver III
Resolver III
In response to ArchStanton

‎03-15-2023 09:22 AM

@ArchStanton 

You have an a single quote " ' " after the DISTINCT(
I think that might be causing it.

nleuck_101_0-1678897251820.png

 

Message 13 of 33
1,831 Views
0
ArchStanton
Post Prodigy
Post Prodigy
In response to nleuck_101

‎03-15-2023 09:34 AM

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

 

ArchStanton_0-1678898029095.png

 

Message 14 of 33
1,824 Views
0
eng23
Regular Visitor
In response to ArchStanton

‎03-21-2023 01:25 PM

Hi may I ask how did you fix that? cause i get the same error.. 

 

eng23_0-1679430307936.png

 

Message 21 of 33
1,732 Views
0
nleuck_101
Resolver III
Resolver III
In response to eng23

‎03-21-2023 01:29 PM

@eng23 

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.

Message 22 of 33
1,725 Views
0
eng23
Regular Visitor
In response to nleuck_101

‎03-21-2023 01:41 PM

yes i have the red line under Slope1 and Intercept, 

but If i dont fix it its showing this error visual..

 

eng23_0-1679431264355.png

 

Message 23 of 33
1,713 Views
0
nleuck_101
Resolver III
Resolver III
In response to eng23

‎03-21-2023 01:45 PM

@eng23 

 

What do the details say?

Message 24 of 33
1,709 Views
0
eng23
Regular Visitor
In response to nleuck_101

‎03-21-2023 01:51 PM

It's saying ...🤔

 

eng23_1-1679431873476.png

 

 

Message 25 of 33
1,697 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All
Top Solution Authors
View All