Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table JobDetail which records start and stop times for a job where the jobdetail table is linked to the job table through the JobId. I currently use a measure to sum the total hours spent per job in the job table. In the Job table, I want to create some bins for the total time such us "Under 1 hour", "1 to 2 hours", etc. Although when using live query, you cant use measures to put jobs into the bins. Any ideas?
Solved! Go to Solution.
Have no idea on what's you data like and what is the execpted output, based on my understanding, for a data as below, to get something as your stated, you can follow the steps.
Hi Brent,
With the January update, Unrestricted measures for directquery are being supported in PowerBI.
To use this feature what you need to do is, select Files --> Option and Settings --> Options menu. Then select "DirectyQuery" in the left pane of the "Options" dialog box. You need to select the checkbox that would "Allow unrestricted measures in DirectQuery mode". Please note that since some of the DAX functions may have toll on the performance, this is not selected by default. After you click on "OK", you will need to restart PowerBI Desktop application, to start using this feature.
Hope this is helpful.
Request you to please mark this as solution, if you find this response relevant.
Regards,
Ashish
Thank you for your reply. I am aware it is unrestricted (the measures) however it still wont allow measures to be used in the calculated columns, hence my roadblock. I want to be able to use it as a dimension for an axis hence why using a measure to calculate it wont work either.
Have no idea on what's you data like and what is the execpted output, based on my understanding, for a data as below, to get something as your stated, you can follow the steps.
Ok. I figured a workaround. I created a calculated column in SQL database to calculate the hours worked for each job detail and then imported that in and used your method to aggregate up to the job table.
Thanks!
I just tried your solution and as soon as I create a new column;
= Table.AddColumn(#"Removed Columns1", "Hours Worked", each Duration.TotalHours([FinishTime]-[StartTime]))
A warning comes up saying this step isnt supported in DirectQuery mode.
Any work around for this?
Thanks for that Eric! Thats what I thought I would need to do, revert to M to solve the issue. Although how did you get the totalHourSpent column? That is based of the hour spent column?
I used a measure so I could use the Calculate function to sum up all JobDetail Hours for each job. Calculated columns have no such benefit.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |