I added a new visual(Line chart) to an app with a new measure. The visual loads very slowly on the desktop app, around 35-40 seconds but does load. When I publish the app the visual does not load and gives me an error saying it could not retrieve the data for the visual. I have another very similar visual/measure that load fine. Also if I filter the data down quite a bit(either with a filter that limits the data to a subset of the entire date range, or if I filter to a smaller date range_ the visual will load. So i think it has more to do with too much data, rather than not being able to retrieve the data. I'd also like to improve the speed of the load as well. I know its a tough ask since I can only share my DAX and not my data, but I am so new to DAX that I am hoping someone can suggest other methods of what I am doing that may have better performance.
So basically what I am trying to do with the below dax is create a couple of date variables, then I separate data from a table based on filters using the date variables. So I basically get policies from 13-24 months ago in one variable, and policies from the current past 12 months in another. I join these 2 into 1 table. What this gets me is a table of the 13-24 old policies, and then another table of those same policies that got renewed and are still in effect in the current past 12 months. I then check for the premium amounts based on the policies in those lists to get a numerator(current policy premium value) and a denominator(policy premium value from 13-24 months ago policies). The result shows how much of the premium we have retained from 1 year to the next. The main table this is looking at is 250M rows. I did some research and learned that the Distinct function is a fairly heavy lift, but I need the lists to be distinct entires only. Not sure if there is a better way to accomplish this.
Measure =
=
VAR maxDate =
LASTDATE ( 'Accounting Date'[Accounting Date] )
VAR minDate =
SAMEPERIODLASTYEAR ( maxDate )
VAR inforceLastYear =
CALCULATETABLE (
DISTINCT ( 'Written Premium'[DIM_POLICY_FK] ),
REMOVEFILTERS ( 'Accounting Date' ),
'Policy Term'[Term Effective Date] <= minDate,
'Policy Term'[Term Expiration Date] > minDate,
OR (
'Policy Term'[Term Cancellation Date] > minDate,
ISBLANK ( 'Policy Term'[Term Cancellation Date] )
)
)
VAR inforceCurrent =
CALCULATETABLE (
DISTINCT ( 'Written Premium'[DIM_POLICY_FK] ),
REMOVEFILTERS ( 'Accounting Date' ),
'Policy Term'[Term Effective Date] <= maxDate,
'Policy Term'[Term Expiration Date] > maxDate,
OR (
'Policy Term'[Term Cancellation Date] > maxDate,
ISBLANK ( 'Policy Term'[Term Cancellation Date] )
)
)
VAR retainedPolicyList =
NATURALINNERJOIN ( inforceLastYear, inforceCurrent )
VAR premiumRetainedPolicies =
calculate(
[Written Premium 12M],
'Written Premium'[Dim_Policy_FK] IN retainedPolicyList,
SamePeriodLastYear('Accounting Date'[Accounting Date])
)
VAR premiumPoliciesInforceLastYear =
calculate(
[Written Premium 12M],
'Written Premium'[Dim_Policy_FK] IN inforceLastYear,
SamePeriodLastYear('Accounting Date'[Accounting Date])
)
VAR result = premiumRetainedPolicies / premiumPoliciesInforceLastYear
RETURN
result
