cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kwinchco Frequent Visitor
Frequent Visitor

Multiple series in line chart...hoping for a better solution than mine

I have managed to solve my issue but I'm struggling to believe it's the best way to do it and I wonder if any of you can help me find a better solution. I'm a PowerBI novice but have been programming for far too long.

 

My issue: I have a SQLServer view which has an id (RequestId), a logged date/time (LoggedDateTime) and a closed date/time (ClosedDateTime) plus lots of other fields.

I already have this in my PowerBI and have lots of charts based on it.

I wish to have a line chart which looks like this solution I've managed to cobble together:

Capture.PNG

To achieve this I've done a union select statement from the original database table:

select 'logged' as type, requestid, loggeddatetime as datetouse 
from vwAllRequestDetailsWithoutHistory_Vanguard where loggeddatetime is not null union select 'closed' as type, requestid, closeddatetime
from vwAllRequestDetailsWithoutHistory_Vanguard where closeddatetime is not null

Within PowerBI I calculate a week ending date (DateTimeWeekEnd) for 'datetouse' and use it as the axis for a line chart visualation. In Legend I have 'type'. In Values I have 'Count of requestid'

 

It's exactly the end result I want, but I'm guessing there's a better way to do this, by using a different visualisation or something.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Multiple series in line chart...hoping for a better solution than mine

Hi @kwinchco,

 

Instead of create a union select statement from the original database table, you could unpivot columns in Query Editor mode to get the same table structure.

1.PNG

 

Add a custom column:

=Text.Start([Datetimetype],6)

2.PNG

 

Then, you can still add [type] to Legend, and place 'Count of requestid' in Values.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Multiple series in line chart...hoping for a better solution than mine

Hi @kwinchco,

 

Instead of create a union select statement from the original database table, you could unpivot columns in Query Editor mode to get the same table structure.

1.PNG

 

Add a custom column:

=Text.Start([Datetimetype],6)

2.PNG

 

Then, you can still add [type] to Legend, and place 'Count of requestid' in Values.

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kwinchco Frequent Visitor
Frequent Visitor

Re: Multiple series in line chart...hoping for a better solution than mine

Thank you, I've tried that and got it working perfectly.

Capture2.PNG

 

I did have to duplicate the original data source within Query Editor to get it working, otherwise it removed the LoggedDateTime and ClosedDateTime from my original query, causing an error as other charts were referencing one/both of those columns.

Is this the expected behaviour or is there something else I should have done?