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.
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.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |