Calculate and Earlier context help

[sreachard]

I'm working on a measure to find the earliest date a customer has an insurance policy (excluding the current policy). The result is current year, by business, insured and policy. For each policy i need to find the earliest date (all time) that a customer had a policy. The current version is within the same business, but i am expecting that will change and will need to take business slicer selections into consideration so i want to keep as a measure and not a calculated column.
Some sample data:
[TABLE="width: 424"]
<TBODY>[TR]
[TD]business
[/TD]
[TD]insured
[/TD]
[TD]policy
[/TD]
[TD]premium
[/TD]
[TD]effdate
[/TD]
[TD]expdate
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD="align: right"]342
[/TD]
[TD]P1
[/TD]
[TD="align: right"]543
[/TD]
[TD="align: right"]1/1/2012
[/TD]
[TD="align: right"]1/1/2013
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD="align: right"]342
[/TD]
[TD]P2
[/TD]
[TD="align: right"]342
[/TD]
[TD="align: right"]3/1/2012
[/TD]
[TD="align: right"]3/1/2013
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD="align: right"]353
[/TD]
[TD]P3
[/TD]
[TD="align: right"]225
[/TD]
[TD="align: right"]2/1/2011
[/TD]
[TD="align: right"]2/2/2012
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD="align: right"]225
[/TD]
[TD]P4
[/TD]
[TD="align: right"]634
[/TD]
[TD="align: right"]1/1/2009
[/TD]
[TD="align: right"]1/1/2010
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD="align: right"]225
[/TD]
[TD]P5
[/TD]
[TD="align: right"]215
[/TD]
[TD="align: right"]2/1/2012
[/TD]
[TD="align: right"]2/1/2013
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD="align: right"]227
[/TD]
[TD]P6
[/TD]
[TD="align: right"]150
[/TD]
[TD="align: right"]2/1/2012
[/TD]
[TD="align: right"]2/1/2013
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD="align: right"]324
[/TD]
[TD]P7
[/TD]
[TD="align: right"]200
[/TD]
[TD="align: right"]6/1/2011
[/TD]
[TD="align: right"]6/1/2012
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD="align: right"]523
[/TD]
[TD]P8
[/TD]
[TD="align: right"]125
[/TD]
[TD="align: right"]3/1/2012
[/TD]
[TD="align: right"]3/1/2013
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD="align: right"]500
[/TD]
[TD]P9
[/TD]
[TD="align: right"]115
[/TD]
[TD="align: right"]3/1/2011
[/TD]
[TD="align: right"]3/1/2012
[/TD]
[/TR]
</TBODY>[/TABLE]

and the dax:

[SIZE=1][SIZE=3]=calculate(min(Table1[expdate]),
[/SIZE]
[SIZE=3]VALUES(Table1[INSURED]),
VALUES(Table1[business]),
all(Table1[EffYear])[/SIZE]
[SIZE=3],all(Table1[policy]),
filter(all(Table1[policy]),
[/SIZE]
[SIZE=3]countrows(filter(table1,Table1[policy]<>EARLIER(Table1[policy])))>0))[/SIZE]
[/SIZE]

this actually works with the sample data, but with an actual data set of 300k rows after 5 minutes it does not finish. i suspect i'm selecting more data than i think i am.

I'll explain what i think the statement is doing and maybe some one can tell me where i'm wrong or if i'm right and there is a better performing way to write it.
get the min expdate
for the current insured
for the current business
all dates (for current insured and business)
all policies (for all dates for current insured and business)

and the count rows is how i am trying to exclude the current policy something I attempted to duplicate from Rob Collie's site Aged Debtors–Dynamic Banding in DAX « PowerPivotPro

here is a link to the actual workbook http://sdrv.ms/OwWR68


Thanks for any help.

 

[JavierGuillen]

Hello,

Just so I understand clearly: For each policy level on the pivot table, you want to see the earliest date in which the customer had *any* insurance policy?

For example, in your sample file, you have

Business:A
Insured: 342
Policy: 543

and I see your measure is retrieving '3/1/2013'.

However, it would appear to me the output should be '1/1/2013' which is the earliest effective date that the current customer had any policy at all

is this correct?

 

[sreachard]

for each policy i need to see the earliest date for the customer excluding the current policy. so for
business A
insured 342
Policy 543

get the min date where business = A and insured = 342 and policy <> 543

so for this example 3/1/2013 is the desired result

the measure works (i think) on this simple data set but on 300k rows, i have to kill it after 5 minutes, it never comes back.

I can get it to work ok on the full data set until i try to exclude the policy of the current row, that makes me think the All(table1[Policies]) is getting all of the policies not just all the policies for the business and insured that i think it is.

I'm struggling with context in DAX every time i think i understand it i prove my self wrong.

Thanks for taking a look

 

[JavierGuillen]

The calculation you are using leverages two nested iterations which is not very efficient when computing over a big number of rows. The technique you mention is generally recommended over small tables, when computing auto-increment numbers in a measure as opposted to a calculated column (more details here: Can EARLIER be used in DAX measures? « Javier Guillén)

Using the following expression, you can get the same output (at the policy level), without the need to iterate:

IF(ISFILTERED(Table1[policy]),
CALCULATE( MIN(Table1[expdate]) ,
ALL(Table1[policy], Table1[EffYear]),
Table1[policy] <> VALUES(Table1[policy])
)
)

Javier Guillen

 

[sreachard]

Thanks, I guess one very important detail i forgot to include is i'm stuck on V1. Our sharepoint farm is 2008r2 and won't be upgraded any time soon. Is there any way to get it to work without the isfiltered().

 

[JavierGuillen]

You can replace ISFILTERED with COUNTROWS(VALUES())

=IF(COUNTROWS(VALUES(Table1[policy])) = 1,
CALCULATE( MIN(Table1[expdate]) ,
ALL(Table1[policy], Table1[EffYear]),
Table1[policy] <> VALUES(Table1[policy])
)
)

I suggest using a flattened pivot table in your case as the values apply at the policy level only.

 

[sreachard]

I should have figured out to use countrows!
Thanks, That works!