Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a meaure that is using the divide function, it's taking two measures as input parameters (CompletedCount and NumberOfDays). Visuals using this measure are pretty slow and I suspect that the NumberOfDays measure is to blame.
What the NumberOfDays measure does is calculate the number of days between a start date and end date. If the end date > today then use today else use end date. here is the measure. Is there a more efficient way to write this?
Day Count = VAR StartDate = MIN ( 'Completed Date'[Date] ) VAR EndDate = IF ( MAX ( 'Completed Date'[Date] ) > TODAY (), TODAY (), MAX ( 'Completed Date'[Date] ) ) RETURN INT ( EndDate - StartDate )
Solved! Go to Solution.
Hi @JimJim
I do not think it can get much more efficient than what you already have. I really doubt the issue is there. Maybe avoiding the double invocation of MAX ( 'Completed Date'[Date] ) like:
Day Count = VAR StartDate = MIN ( 'Completed Date'[Date] ) VAR EndDate = MAX(TODAY(), MAX ( 'Completed Date'[Date] ) )
RETURN INT ( EndDate - StartDate )
but the change, if any, will be minimal since the engine is quite efficient in calculating operations like MAX ( 'Completed Date'[Date] ).
Have you tried DAX Studio? It's a very useful tool to pinpoint performance bottlenecks.
Cheers
Hi @JimJim
I do not think it can get much more efficient than what you already have. I really doubt the issue is there. Maybe avoiding the double invocation of MAX ( 'Completed Date'[Date] ) like:
Day Count = VAR StartDate = MIN ( 'Completed Date'[Date] ) VAR EndDate = MAX(TODAY(), MAX ( 'Completed Date'[Date] ) )
RETURN INT ( EndDate - StartDate )
but the change, if any, will be minimal since the engine is quite efficient in calculating operations like MAX ( 'Completed Date'[Date] ).
Have you tried DAX Studio? It's a very useful tool to pinpoint performance bottlenecks.
Cheers
Hi @AlB ,
Thanks for your reply, I didn't know that aggregate functions such as MAX could take multiple expressions so that is really good to know. However, I need to take the earliest date between CompletedDate and TODAY so the variable should be:
VAR EndDate = MIN(TODAY(), MAX ( 'Completed Date'[Date] ) )
Funnily enoough the visuals appear to be working fine just now (I have reopened the report to remove any caching and it's still running fast) so I don't think DAX studio would be much help at this moment. Maybe just one of these glitchy things, I will keep an eye on it.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |