Forum Discussion

BrentA's avatar
BrentA
Icon for Advocate II rankAdvocate II
10 years ago
Solved

Live query limitation workaround

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 pe...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    BrentA

    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.

     

    1. Click "Edit queries" and go to the query edit window.
    2. Right click on the query and duplicate the query.
    3. Group by the duplicated query and create a column based on the aggregated hours.
    4. Merge the Original query with the grouped by query.


    5. If you have any question, feel free to let me know.
BrentA's avatar
BrentA
Icon for Advocate II rankAdvocate II
10 years ago

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.

Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

BrentA

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.

 

  1. Click "Edit queries" and go to the query edit window.
  2. Right click on the query and duplicate the query.
  3. Group by the duplicated query and create a column based on the aggregated hours.
  4. Merge the Original query with the grouped by query.


  5. If you have any question, feel free to let me know.
  • BrentA's avatar
    BrentA
    Icon for Advocate II rankAdvocate II
    10 years ago

    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?

  • BrentA's avatar
    BrentA
    Icon for Advocate II rankAdvocate II
    10 years ago

    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?

  • BrentA's avatar
    BrentA
    Icon for Advocate II rankAdvocate II
    10 years ago

    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!