Forum Discussion
Dax Measure help
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])
Thank you for your reply
I want to just see result using card visual that means
measure is alone and not used along PolicyNumber.
hope u understood
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.
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.
if we select 7/4/2016 on slcier then it should consider all claims until 7/4/2016
and not just those claims that are on 7/4/2016
