Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Apologies for the multiple posts, but I am getting so down because nobody seems clear on my problem, and I'm no closer to a solution after 3 weeks of asking. I am unable to delete the prior posts, but in the interests of keeping things simple I won't link them here (if you want to search for them on my profile, be my guest). My end goal is to produce a visual on a stacked column chart that displays, over the course of any 24hr period (or last 28 days' worth, etc) separated into 2hrly blocks, the total number of workers on duty, vs the total duration of care interactions with residents during those same 2hrly blocks. In other words, how many members of staff creating 120mins (if they worked across both hours) of availability were in surplus compared to the total time taken caring for residents in that same period.
I have two queries - Care Interactions and StaffInteractions.
Care Interactions is a list of interactions showing help given to them during the day / night. Columns in PQ are:
Date = date type, Time = Time type, Duration = whole number (in minutes), TimeSlot = a custom column determining if the Time field is within 2hr blocks (eg 23:59 = 10pm-12am, 14:05 would be 2pm-4pm etc), SortOrder is another custom column that orders TimeSlot into the correct time basis (eg 12am-2am = 1, 2am-4am = 2, 4am-6am = 3 etc), and for reasons of trying to keep things less convoluted, TimeTaken is #Duration(0,0,[Duration],0).
In what I know and will refer to as the DAX editor (the table icon underneath the report view), this is the layout.
I have an extra column, New Duration, which is the forumula above it. I had read somewhere that showing Durations converted in PQ can sometimes not work correctly, so I thought I'd use this as well to see if this worked.
Staff Interactions is a list of staff members showing the hours they were available during their shift. Columns are:
Started = date/time field, Finished is also. Minutes worked = whole number, TimeSlot (works the same as that for Care Interactions), SortOrder (again, works as for CI), Date is a custom column that derives if the TimeSlot in question is during the Started date, or the Finished date (ie 01/07/2020 10pm is 01/07, but if they worked a night shift then at 3am that care availability would be in 02/07), and TimeTaken is also #Duration(0,0,[MinutesWorked],0).
In DAX:
Again, an extra column called New TimeTaken, which works in the same way as for CI's New Duration.
There are no relationships between the two queries, but should there be? In what is probably an incorrect setup, I use TimeSlot from either query as the shared axis, and then apply both of the New Duration to the Column Values. I get this:
If I remove TimeSlot from one query and replace it in the visual with the other's, then I get the reverse where New Time Taken displays correctly, but New Duration is a common counted value. In the visual above, the New Duration shows correctly, but New TimeTaken does not. Below is what New TimeTaken should display by itself:
I've tried several times to explain this, and I'm sorry if it's annoying anyone. I just need to be able to display this visual as follows (I have done this by overlaying two visuals of the two queries):
Please help! What am I doing wrong?
Thanks.
Hi @Anonymous,
if I understand you correctly, you need a query / table with all TimeSlots.
You relate this query / table to both queries and use them on the shared axis.
Hi @mwegener
I am still new to this. Could you elaborate a little more?
Are you saying I need to create a new table with 12am-2am, 2am-4am.....10pm-12am and then relate both of the queries mentioned's TimeSlots to it?
Thanks.
Hi @Anonymous ,
yes, you understood that correctly.
Hi @mwegener
Quick update. I did as you advised, and I was still not getting any usable data. I then realised that although my Date field in one query was set via a filter on the visualisation, the other query had not. So I dragged the second query's date field into the filters pane and put the same criteria (usually 28days) and then double checked the data against a manual SQL statement. They now match up, so in theory my problem is resolved.
However, is there a way to universally apply a nice looking slicer that can do this in one go, rather than manually editing both filters?
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |