Combine two, essentially similar yet disparate queries to display in one chart

[Anonymous]

Hi. I've posted this already in an existing question of mine, but fear it's lost in the wash so asking again. I'm still new to PBI and my SQL is adequate but not expert by any stretch, and I'm in need of help. 

I have two queries: one showing all interactions recorded for an individual over a time frame, and another showing the number of staff that were at work over the same time frame. 

Query 1 - individual interactions

Duration is a Duration datatype, showing minutes. "Slot" is a conditional column that assigns 12-2am, 2am-4am, 4am-6am......8pm-10pm, 10pm-12am depending on the interaction Date and Time. Slot is then set to sort by Custom, which is another conditional column that shows 1 - 12 depending on the two-hour Slot, in order for it to show in the right order on the chart. 

Query 2 - staff on duty

There is no data I can utilise that says "between 10pm and 12am, X staff were on duty". I only have start date/time, finish date/time. With the help of others, I created a query that shows me this data:

TimeOrder is a conditional column that says ie 'if [HourPeriod] = "0-1" then "12am-2am"', all the way through to 23-0. This was done to make the data similar to Query 1. Order is a sorting column that converts 12am-2am = 1, 2am-4am=2 etc so that it too displays properly on the chart. (I still think it's rubbish that PBI cannot account for these without silly workarounds, but there we go). I added another custom column, Custom, to convert the date from Started but really this serves no purpose. 

This produces similiar charts which align well and give data that is meaningful to me:

 

However, I want to combine the two as such:

I do not know how to do this effectively as I am not skilled enough to work it out. Does anyone have any ideas?

[Pragati11]

Hi @Anonymous ,

Try something like this:

Create a Line and Clustered column chart.

Move your both metrics "Care Hours" and "People Working" to the COLUMN VALUES. 

For refrence, I am attaching a chart for the same:

Let me know if this helps:

Thanks,

Pragati

[Anonymous]

@Pragati11 

Thank you. I've done this. I also had to put some shared axis information on:

The date hierarchy is from the individual interactions query, and both column values are set to Sum. This is what I get:

The taller columns is for the staff interactions data. I'm obvioulsy still missing something here. I have also got the following relationships defined as without them, nothing is particularly meanginful:

 Slot is linked to TimeOrder. Date is to Custom in SI. Duration is to Duration. 

At "Day" level in the hierchy, I get this:

Still confused!

[Pragati11]

Hi @Anonymous ,

You will have to be little clear on what is your confusion. 

The chart shows what you were trying to achieve.

Also, why there are 3 INACTIVE relationships between these 2 tables. There should always be atleast one ACTIVE relationships between 2 tables.

Thanks,

Pragati

[Anonymous]

This is where I say my naivety comes in. I set them up and it said that they were active? 

What I am after is this (I've stacked the originals on top of each other, and changed the colour)

Apologies for the noobiness, but I'm doing the best I can!

 

[Pragati11]

Hi @Anonymous ,

Try using a Stacked Column Chart visual in Power BI, like below:

Thanks,

Pragati

[Anonymous]

Sorry. My issue isn't really that they're on top of each other or side by side. I get that both of these configurations would work. My issue is that the data isn't right in this view. 

Ie 12-2am, is about 7hr for care interactions, and about 15hrs for staff hours. Not 255

[Anonymous]

I believe I'm stumbled upon the cause. In Power Query it's a Duration, but in Dax it shows as a decimal. For some reason I don't quite understand, whichever chart I use (care interactions or staff on duty), the 'foreign' total I want to include to show hours worked vs interactions time taken displays correctly, the other is just a total of those decimal figures. 

Does anyone know how to get past what feels like a bug, but probably isn't, by converting this to something or using a new column to allow the chart to use the data as Duration so that it displays correctly?

[Icey]

Hi @Anonymous ,

Please check if this post could meet your requirement: Chelsie Eiden's Duration.

Best Regards,

Icey

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