cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Unable to use two decimal values from two separate queries in one visual

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:

 

Screenshot 2020-07-30 at 10.04.38.png

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. 

Screenshot 2020-07-30 at 10.12.07.png

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:

 

Screenshot 2020-07-30 at 10.18.24.png

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:

 

Screenshot 2020-07-30 at 10.18.47.png

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:

 

Screenshot 2020-07-30 at 10.27.59.png

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:

 

Screenshot 2020-07-30 at 10.30.07.png

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

 

Screenshot 2020-07-30 at 10.33.04.png

Please help! What am I doing wrong?

 

Thanks.

 

 

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Unable to use two decimal values from two separate queries in one visual

Hi @antmit

 

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.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

Highlighted
Helper III
Helper III

Re: Unable to use two decimal values from two separate queries in one visual

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. 

Highlighted
Super User IV
Super User IV

Re: Unable to use two decimal values from two separate queries in one visual

Hi @antmit ,

 

yes, you understood that correctly.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

I work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #BetterTogether
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website

Highlighted
Helper III
Helper III

Re: Unable to use two decimal values from two separate queries in one visual

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?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors
Users online (664)