Line chart will only display dots - no lines

[CH_MJR79]

I have added a Line Chart visual to my Power BI file, however whilst the dots are in the correct positions there is no line joining the dots up.

The X-Axis shows four years - 2020 to 2023 which are taken from a Years table. The Y-Axis has sales totals for the four years. The Years table is linked to a corresponding Year column on each of the Sales tables.

The dots on the chart are showing in the correct positions, and hovering the mouse over each dot shows the correct sales totals.

Other charts, e.g. a Bar Chart, Pie Chart etc. all work fine.

What am I missing here?

This is a screenshot of the chart:

[CH_MJR79]

Thanks @v-xinruzhu-msft.

Also resolved via this link as well - it required a single measure for all sales. In error I was using two separate measures.

[Gayatri_D05]

Hi @CH_MJR79 ,
From the legend I am assuming you have used 4 separate measures and have added them in X-Axis, which is why theres no line connecting the dots. You would need to just use a single Sales measure and put it in X-axis. It would give you the expected behaviour.

If thats not the case (4 measures in X-axis), could you please share some more insights about the visuala and the data going into it ?

If your requirement is solved, please make THIS ANSWER as SOLUTION and help other users find the solution quickly. Please hit the LIKE button if this comment helps you. 😊


 

[_AAndrade]

Could you can share more pictures of data table and x-axis ?

[CH_MJR79]

Yes of course. Please see below:

The data source is an Excel sheet with four tabs - one for each of the years:

This is the separate Years table:

These are the relationships:

And the X and Y axis:

[_AAndrade]

Is there any reason not to merge all the sales tables into a single table?

[CH_MJR79]

Funnily enough the original data source does have the year columns all on the same tab.

I changed the years so they were on separate tabs as I thought it might make it easier.

Having them all on the same tab would be better in hindsight though.

Is there a way of making the line chart work with the figures all on the same Excel tab?

In the screenshot below, the Sales total would be the figures for account numbers 1, 2, and 3 added together per year. So the 2023 Sales total would be cells C2, C3 and C4 added together. The 2022 Sales total would be cells D2, D3 and D4 added together, and so on.

[v-xinruzhu-msft]

Hi @CH_MJR79 

As @Gayatri_D05  mentioned, you can create one measure then put it to the Y-axis.

e.g 

Measure =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Years'[Year] ) = 2020, SUM ( '2020'[Sales] ),
    SELECTEDVALUE ( 'Years'[Year] ) = 2021, SUM ( '2021'[Sales] ),
    SELECTEDVALUE ( 'Years'[Year] ) = 2022, SUM ( '2022'[Sales] ),
    SELECTEDVALUE ( 'Years'[Year] ) = 2023, SUM ( '2023'[Sales] )
)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CH_MJR79]

I would need the four measures (or be able to represent the four measures on the Y-Axis.

@v-xinruzhu-msft when I added your example measure it didn't display anything unfortunately:

This is the formula I used:

Measure =

SWITCH(

    TRUE(),

    SELECTEDVALUE('Years'[Year]) = 2020, SUM('2020'[SalesCodes-2020]),

    SELECTEDVALUE('Years'[Year]) = 2021, SUM('2021'[SalesCodes-2021]),

    SELECTEDVALUE('Years'[Year]) = 2022, SUM('2022'[SalesCodes-2022]),

    SELECTEDVALUE('Years'[Year]) = 2023, SUM('2023'[SalesCodes-2023])

)

For reference, this is what the SalesCodes formulas look like:

SalesCodes-2020 = IF(AND('2020'[Account] >= 1, '2020'[Account] <= 10), '2020'[Year End 30/6/2020], BLANK())*-1

Many thanks for your help on this so far.

[CH_MJR79]

I thought it might help if I put some screenshots below of the way the line chart appears, along with a screenshot of the data source.

I have mocked this up by entering some dummy data manually into the tables.

1. This is how the chart currently looks with the dots above each other:

2. This is the data in the table:

[CH_MJR79]

I have just created a new file, entered some dummy data and then added a line chart.

This line chart also just displays the coloured dots - no lines joining them up.

I am obviously doing something wrong but can't seem to work out what.

Here are the screenshots of the new file:

1. The chart:

2. The tables:

2022:

2023:

Date:

3. Relationships:

[v-xinruzhu-msft · Accepted solution]

Hi   @CH_MJR79 

Please refer to the following solution.

Sample data 

2022

2023

The relationship

Create a measure

Measure = SWITCH(TRUE(),SELECTEDVALUE('Date'[Dates].[Year])=2022,SUM('2022'[Total]),SELECTEDVALUE('Date'[Dates].[Year])=2023,SUM('2023'[Total]))

Then just input the date column and measure to the visual.

Output

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.