How to get data from measure variables into a table or export for more detailed inspection?

[Anonymous]

Created the following measure and I am not sure its working as intended. I want to be able to view the data from some of the variables in more detail. 

Premium Retention Rate 12M:=
VAR maxDate =
LASTDATE ( 'Date'[Date] )
VAR minDate =
SAMEPERIODLASTYEAR ( maxDate )
VAR inforceLastYear =
CALCULATETABLE (
DISTINCT ( 'Premium'[DIM_POL_FK] ),
REMOVEFILTERS ( 'Date' ),
'Term'[Term Effective Date] <= minDate,
'Term'[Term Expiration Date] > minDate,
OR (
'Term'[Term Cancellation Date] > minDate,
ISBLANK ( Term'[Term Cancellation Date] )
)
)
VAR inforceCurrent =
CALCULATETABLE (
DISTINCT ( 'Premium'[DIM_POL_FK] ),
REMOVEFILTERS ( 'Date' ),
'Term'[Term Effective Date] <= maxDate,
'Term'[Term Expiration Date] > maxDate,
OR (
'Term'[Term Cancellation Date] > maxDate,
ISBLANK ( 'Term'[Term Cancellation Date] )
)
)
VAR retainedPolicyList =
DISTINCT ( NATURALINNERJOIN ( inforceLastYear, inforceCurrent ) )

VAR premiumRetainedPolicies =
calculate(
[Written Premium 12M],
'Premium'[DIM_POL_FK] IN retainedPolicyList,
SamePeriodLastYear('Date'[Date])
)
VAR premiumPoliciesInforceLastYear =
calculate(
[Written Premium 12M],
'Premium'[DIM_POL_FK] IN inforceLastYear,
SamePeriodLastYear('Date'[Date])
)
VAR result = premiumRetainedPolicies / premiumPoliciesInforceLastYear
RETURN
result

I would like to be able to get a list of the DIM_POL_FK's from the inforcelastyear and inforce current VARs, and ultimately be able to see a list of the POL FK's and Written Premium 12M for premiumPoliciesInforceLastYear and premiumRetainedPolicies. I tried just creating a table using the code for the variables. One table produced 0 rows and the other produced 28k. But I am expecting close to 250k rows for retained and 290k rows for last year.

[Anonymous]

Hi @Anonymous ,

To get a list of the DIM_POL_FK's from the inforcelastyear and inforce current VARs, you can create a table using the following code:

InforceLastYearTable = ADDCOLUMNS(
    SUMMARIZE('Premium', 'Premium'[DIM_POL_FK]),
    "InforceLastYear", [inforceLastYear]
)

InforceCurrentTable = ADDCOLUMNS(
    SUMMARIZE('Premium', 'Premium'[DIM_POL_FK]),
    "InforceCurrent", [inforceCurrent]
)

Then, to see a list of the POL FK's and Written Premium 12M for premiumPoliciesInforceLastYear and premiumRetainedPolicies, you can use the following code:

PremiumPoliciesInforceLastYearTable = ADDCOLUMNS(
    FILTER('Premium', 'Premium'[DIM_POL_FK] IN inforceLastYear),
    "PremiumPoliciesInforceLastYear", [Written Premium 12M]
)

PremiumRetainedPoliciesTable = ADDCOLUMNS(
    FILTER('Premium', 'Premium'[DIM_POL_FK] IN retainedPolicyList),
    "PremiumRetainedPolicies", [Written Premium 12M]
)

The first code block creates two tables, one for inforce last year and one for inforce current, with the DIM_POL_FK's as the rows and the inforceLastYear and inforceCurrent VARs as the columns. The second code block creates two tables, one for premium policies in force last year and one for premium retained policies, with the DIM_POL_FK's as the rows and the Written Premium 12M as the columns.

Note that the code you provided is incomplete, so I cannot guarantee that these code blocks will work as intended.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.