cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stokidez
Helper III
Helper III

Line Graph Help Needed

Hi,

 

I'm trying to show when the following contract dates in a line graph. For some reason a number of the records are not diplaying correctly. Data is below (only 7 of the ~20 or so records with a date are showing on the line graph).

 

stokidez_0-1620644940717.png

 

I've created a 'Date' table as such

stokidez_1-1620645007032.png

 

This date table has an active link to the Planned Contract Signed Date

stokidez_2-1620645078581.png

 

In terms of the visual this is how its setup. The Value field is not the Planned Contract Signed Date above but rather a measure that has the following calculation:

 

Planned Contract Signed Measure = CALCULATE(COUNT('Procurement Plan'[Title]),USERELATIONSHIP('Procurement Plan'[Planned Contract Signed Date],'Date Table'[Date]))

stokidez_3-1620645172458.png

 

Any help much appreciated!

 

 

 

1 ACCEPTED SOLUTION

Hey @stokidez ,

 

I found a solution but still didn't fully understand the problem 😅

So it seems like the connection between the date table and the procurement table are not working for all the dates:

selimovd_0-1620680416316.png

 

I've seen that again in the past. As far as I remember it was the mix of different date formats, like one source was in US format and one in European format. Although the dates show the right values the connection didn't work. If I remember right, back then we could solve that with the "Import from locale" function in Power Query.

 

In your case I just created for both tables each a new ID-column. Usually I personally would do that in Power Query, but as I don't have access to your source I did it in DAX:

Date Table = 
ADDCOLUMNS(
    CALENDAR(DATE(2019,01,01), DATE(2024,01,01)),
    "DateID", FORMAT([Date], "YYYYMMDD")
)

And as a calculated column:

ContractSignedDateID = FORMAT('Procurement Plan'[Planned Contract Signed Date], "YYYYMMDD")

 

And changed the relationship to the ID columns:

selimovd_1-1620680605652.png

 

And then all the dates are properly connected:

selimovd_2-1620680678484.png

selimovd_3-1620680692665.png

 

See here my result file:

https://www.swisstransfer.com/d/7dde930d-6c80-4178-ada2-705e1f358c11

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

View solution in original post

13 REPLIES 13
selimovd
Super User
Super User

Hey @stokidez ,

 

if you have the relationship 'Date Table'[Date] - Procurement Plan'[Planned Contract Signed Date] you don't need to use the USERELATIONSHIP function. This would overrule a relationship if you would like to use another relationship.

 

You can just use COUNT('Procurement Plan'[Title])

 

What exactly is not correct in the graph? Can you give a more detailed example?

I see a few single records with date and then looking at the scrollbar a lot of rows without a date. So for me the visual seems to make sense.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Hi @selimovd 

 

Thanks for the message and the note on USERRELATIONSHIP - i've removed this part from the measure.

 

Hovering over the individual months in the line graph it is picking up these number of records:

March - 1 record

April - 1 record

May - 3 records 

June - 1 record

August - 1 record

 

Why isn't it picking up anything before March 2021 or after August 2021? I've not selected any filtering by dates. The dates I wish it to pick up are below.

 

stokidez_0-1620646466289.png

 

Thanks again,

 

Hey @stokidez ,

 

now I see your point. That's a pretty good question.

A few of the rows would be ignored as COUNT is just counting non blank values. I personally would try COUNTROWS('Procurement Plan') as it's more efficient than COUNT and in your case counting the amount of rows for a specific context.

 

Can you share the file or a basic version of the file if it is confidential?

 

That would make it easier to see where the error happens.

 

Thank you and best regards

Denis 

Hi @selimovd unfortunately it won't let me copy/paste the excel table into here (post flooding detected error) or upload the cut down pbix file I've created (pbix file not supported error).

 

Here is a snapshot of the data

stokidez_0-1620654275093.png

 

I would expect to see the following on the visual

December 2020 - 1

January 2021 - 2

February 2021 - 8

March 2021 - 2

May 2021 - 3

June 2021 - 4

August 2021 - 1

November 2021 - 1

December 2021 - 1

January 2022 - 1

 

Thanks again,

Hey @stokidez ,

 

I need the PBIX file to help you.

Can you share that on Dropbox or  OneDrive or swisstransfer.com and post the link here?

 

Best regards

Denis

Hi @selimovd 

 

Thanks again for your help - much appreciated.

Hey @stokidez ,

 

I found a solution but still didn't fully understand the problem 😅

So it seems like the connection between the date table and the procurement table are not working for all the dates:

selimovd_0-1620680416316.png

 

I've seen that again in the past. As far as I remember it was the mix of different date formats, like one source was in US format and one in European format. Although the dates show the right values the connection didn't work. If I remember right, back then we could solve that with the "Import from locale" function in Power Query.

 

In your case I just created for both tables each a new ID-column. Usually I personally would do that in Power Query, but as I don't have access to your source I did it in DAX:

Date Table = 
ADDCOLUMNS(
    CALENDAR(DATE(2019,01,01), DATE(2024,01,01)),
    "DateID", FORMAT([Date], "YYYYMMDD")
)

And as a calculated column:

ContractSignedDateID = FORMAT('Procurement Plan'[Planned Contract Signed Date], "YYYYMMDD")

 

And changed the relationship to the ID columns:

selimovd_1-1620680605652.png

 

And then all the dates are properly connected:

selimovd_2-1620680678484.png

selimovd_3-1620680692665.png

 

See here my result file:

https://www.swisstransfer.com/d/7dde930d-6c80-4178-ada2-705e1f358c11

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Wow @selimovd thanks for your perseverance! I would not have got there without your help

@stokidez you're welcome, I'm happy it works now 😊

Hi @selimovd I've noticed when I refresh my data and it pulls through the latest information from the SharePoint, it loses its connection to the date table. The data in the date table has not changed (its still present), the contract signed date field and measure is still populating and the data connection between the two tables are still active.

 

Any ideas? Thanks!

Hey @stokidez ,

 

it can be with the first refresh that it looses the connections.

Just redo them. But then this should not happen again after the next refresh.

 

Best regards

Denis

Hi @selimovd I see what it's doing now (although I'm not sure why!) Is there a way of forcing it so it doesn't automatically change format on refresh? Thanks, 

 

Before refresh:

stokidez_0-1620744373541.png

 

After refresh:

stokidez_1-1620744382237.png

 

 

 

Hey @stokidez ,

 

it doesn't seem to be stable.

I would try to import the data in Power Query using the locale function:

Change Type Using Locale with Power Query • My Online Training Hub

 

Then add a new column for the ID:

Date.ToText([Planned Contract Signed Date], "yyyyMMdd")

 

The Date table you can still do in DAX.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.