Forum Discussion

vickyprudhvi's avatar
vickyprudhvi
Icon for Helper IV rankHelper IV
10 years ago

Dax Measure help

DateDimensionID JobCloseDate PolicyNumber BranchID Job 20160701 7/1/2016 0:00 A 1 Submission 20160702 7/2/2016 0:00 A 3 Policy Change 20160702 7/3/2016 1:00 C 4 Reinstatem...
Need Help
KGrice's avatar
KGrice
Icon for Memorable Member rankMemorable Member
10 years ago

Using your sample data, I'm able to come up with 2 if I select 7/3/2016, but 0 if I select 7/4/2016. I think that's accurate from what I can see in the table. On 7/4/2016, the only PolicyNumber is A, and it's most recent Job is Reinstatement. With none of the dates selected, I get this for a final product:

 

 If that looks good, then I did it with 3 measures. You don't have to display all of them in your end result.

 

MostRecentJob = IF(HASONEVALUE(TableName[PolicyNumber]), 
	CALCULATE(
		VALUES(TableName[Job]), 
		FILTER(ALLEXCEPT(TableName, TableName[PolicyNumber]), TableName[JobCloseDate]=MAX(TableName[JobCloseDate]))
	), BLANK())

 

isCancelled = IF([MostRecentJob]="Cancellation", 1, 0)

 

CancelledPolicies = SUMX(VALUES(TableName[PolicyNumber]), [isCancelled])

 

 

 

 

 

 

 

vickyprudhvi's avatar
vickyprudhvi
Icon for Helper IV rankHelper IV
10 years ago

KGrice

this is how I want to see.

but as of 7/4/2016 it should show 1 but shows 0

  • KGrice's avatar
    KGrice
    Icon for Memorable Member rankMemorable Member
    10 years ago

    Hi vickyprudhvi. What I provided above will work for using with a card without further modification:

     

     What I'm not sure of is why selecting 7/4/2016 would result in 1 with the data provided. The only Policy on that date is A, with a Job of Reinstatement.

     

    • vickyprudhvi's avatar
      vickyprudhvi
      Icon for Helper IV rankHelper IV
      10 years ago

      Hi KGrice

      The table is Type 2  fact so only delta's and new Policies will be added. so as of 07/04/2016 it should consider all the claims whose max  jobclosedate has Job type cancellation.