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

[kwinchco]

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:

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.

[v-yulgu-msft · Accepted solution]

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.

Add a custom column:

=Text.Start([Datetimetype],6)

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

Best regards,

Yuliana Gu

[kwinchco]

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

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?