Line Graph Help Needed

[stokidez]

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).

I've created a 'Date' table as such

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

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]))

Any help much appreciated!

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[stokidez]

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.

Thanks again,

[selimovd]

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 

[stokidez]

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

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,

[selimovd]

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

[stokidez]

Hi @selimovd 

Thanks again for your help - much appreciated.

[selimovd · 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:

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:

And then all the dates are properly connected:

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[stokidez]

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

[selimovd]

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

[stokidez]

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!

[selimovd]

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

[stokidez]

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:

After refresh:

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic