I have these columns:
I’m using this DAX calculation:
The result is working fine…
…Until I add another month:
Then I get this error:
My best idea was to DIVIDE the SECONDS by 3600 to get HOURS:
Hoping for smaller units = less bits = no error:
But to no avail, same error appears:
Additional Info:
Fact Table is DirectQuery from SQL Server
32M+ Rows over 5 years
9 Columns
Anyone have any idea on how to resolve this?
I’ve run into a similar problem, except I was trying to filter based on a relationship table and anything past four layers wouldn’t work. There is a limit to how much can be calculated in a single measure or table, and what helped me was using a CALCULATE function to filter the measure to precisely what I wanted it to look at. Wrapping your entire equation into a CALCULATE function with filter contexts of the transactions times might help.
LOVE! but need some guidance here. Is there anything I can add so you can provide a more visual explanation?
I did a quick mock-up in DAX Studio. The second one shows it in a calculate function, so now we need to decide how we want to filter the transaction times. It’s possible the second equation by itself will help, but the syntax probably won’t work.
You could put them into filter functions to make sure it doesn’t go back too far, or you could use the KEEPFILTERS function to try and limit what the measure is trying to calculate. Here’s an article on that function and how it works within a CALCULATE function:
Transaction Duration BEFORE (sec) =
SUMX (
bm_Transactions,
DATEDIFF (
bm_Transactions[Transaction Start Time],
bm_Transactions[Transaction End Time],
SECOND
)
)
Transaction Duration AFTER (sec) =
CALCULATE (
SUMX (
bm_Transactions,
DATEDIFF (
bm_Transactions[Transaction Start Time],
bm_Transactions[Transaction End Time],
SECOND
)
),
bm_Transactions[Transaction Start Time],
bm_Transactions[Transaction End Time]
)
The name of the game here is to use the CALCULATE function and it’s conditions to make the measure calculate as little information as possible.
Was that able to help with some of the issues you were facing?
No luck so far. Mostly DAX errors.
Tried this:
Returns same values, which is good:
but same error occurs when I add another month:
Also tried this, with same error:
Dang. The only thing I can think of is to add a KEEPFILTERS argument for the year in case it’s calculating other years in the background.
Otherwise, you may have to settle on a rolling 4 months for seconds.
Something else I just thought of is to try calculating with minutes and converting it to seconds. Hopefully, the calculation load will be reduced enough for a full year.
I currently have rolling quarters as a temporary work around.
I actually have a DATEDIFF with DAYS granularity, but it returns an INT and rounds off the seconds, so I can convert it back. I haven’t tried minutes yet, let me try that now.
Edit: Yeah, same issue with DATEDIFF by MINUTE granularity, it truncates the seconds and returns an INT. Which removes the ability to convert back to seconds.
And, I definitely can’t settle for it because a ton of transactions with durations under 60 seconds become 0 minutes as the image below shows:
Have you tried taking the aggregated integer and converting it back to a DateTime with CONVERT?
I have not, but then what do I do with the DateTime value? Will I aggregate it again? When will the total seconds appear on my monthly chart?
I’m not entirely sure, it’s a little hard without a file to work with. I was thinking you might be able to wrap the output in the original equation in a CONVERT function to override it turning it into an integer. You’re unfortunately pushing the boundaries of DAX’s ability to compute at the moment.
I did this but same error:
I wish there was an easy way for me to share a PBIX file without all the PII information
Here’s a link to a post on methods of doing so:
And after looking at your equation again you’ll want to change “INTEGER” to “DATETIME”. It’s asking what you want to convert it to.
I’ll check this out…
some interesting news…
If I remove DATEDIFF and just use a Subtraction operator…
…some kind of DateTime value becomes visible for all the months:
which seems like the difference in seconds moving backwards from the beginning of data-time (12/30/1899 00:00:00):
so when I reverse the order of the operand:
the difference is the number of seconds moving forward starting from the beginning of data-time (12/30/1899 00:00:00):
soooo with this DAX, I think it worked:
buuuut, still breaking at the visual:
An interesting breakthrough! I’ll brush on DAX optimization methods - we’re probably running into issues with the internal formula engine as it can only run on a single core. Let’s also see if you’re able to mask the PBI file to share.
I find it fascinating working on issues like these where you’re pushing the absolute boundaries of what PBI can do.
Update: If you’re able to split your transaction date column into a date column and a time column (without a calculated column, so in your query) that has a lot of potential for optimization.
Do you mean like a SUMMARIZE/ADDCOLUMN virtual table type of DAX query? Unless there’s another way to split columns in the query.
