Forum Discussion

LegendaryTokash's avatar
LegendaryTokash
Frequent Visitor
7 years ago

Custom Columns or Measures in a DirectQuery report

All,

 

I have a set of reports that i configured to read data from a given application database.   I love using the DirectQuery mode for these reports, as I query from a DR instance which is fresh enough to be useful, but doesn't negatively impact performance of the production database.

 

That said, I am getting data back with several columns containing time data.   These would be start times for various steps, and they are stored in their own columns.  

 

I am trying to find a method to calculate the difference in two time column values for each row.  Simplified, any given row will have a schedueld time, an actual start time, and a completed time.   I am looking to measure the time difference between at least two of them to then visualize the elapsed time that was calculated, per row. 

 

I found a couple of options - quick measures, but couldn't get it to properly measure the time difference instead of automatically reverting to counts.  Maybe i am doing it wrong?

 

I found an option to create a custom column and appropriately create a formula.  in that case, it worked, but forced the storage mode to import, instead of directquery.   I'd really like to avoid that option - but that is why I am reaching out here, to see if i can find an alternative that gets me the data I want, without having to change out of DirectQuery.

 

Thoughts?

Need Help

1 Reply

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi LegendaryTokash

    You could consider this DAX function and use it in a measure.

    DATEDIFF

    Syntax:

    DATEDIFF(<start_date>, <end_date>, <interval>)

    It returns the count of interval boundaries crossed between two dates.

     

    Best Regards

    Maggie