Forum Discussion
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 | Reinstatement |
| 20160703 | 7/3/2016 1:00 | A | 5 | Cancellation |
| 20160703 | 7/3/2016 2:00 | B | 7 | Cancellation |
| 20160703 | 7/3/2016 3:00 | B | 8 | Cancellation |
| 20160703 | 7/3/2016 4:00 | A | 6 | Cancellation |
| 20160704 | 7/5/2016 0:00 | A | 9 | Reinstatement |
Hi Friends,
I am trying to write a measure in tabular which gets me count of all the policies whose latest Job is "Cancellation"
latest Job is based on JobCloseDate.
For example
IN Power BI Desktop If I have a slicer on Td_Date[CalendarDate](It is connected to this table using DateDimensionID)
If I select 07/04/2016 on the slicer then the totalCount should be 1
If I select 07/03/2016 on the slicer then the totalCount should be 2
Kindly help me out on this.
9 Replies
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.
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
In you provided table, the type of DateDimensionID is not standard Date type, so I create a new date column with following formula and create relationship with calendar table with this column.
Date = DATE ( LEFT ( Table1[DateDimensionID], 4 ), MID ( Table1[DateDimensionID], 5, 2 ), MID ( Table1[DateDimensionID], 7, 2 ) )
Then we can create another measure to display the result in the card.
Measure = VAR CountCurrentDay = CALCULATE ( DISTINCTCOUNT ( Table1[PolicyNumber] ), FILTER ( ALL ( Table1 ), Table1[Date] = MAX ( Td_Date[Date] ) && Table1[Job] = "Cancellation" ) ) RETURN ( IF ( CountCurrentDay > 0, CountCurrentDay, CALCULATE ( DISTINCTCOUNT ( Table1[PolicyNumber] ), FILTER ( ALL ( Table1 ), Table1[JobCloseDate] = CALCULATE ( MAX ( Table1[JobCloseDate] ), FILTER ( ALL ( Table1 ), Table1[JobCloseDate] < MAX ( Table1[Date] ) ) ) && Table1[Job] = "Cancellation" ) ) ) )Best Regards,
Herbert
Thank you for ur detail reply.
What i would like to see is
on 07/04/2016 it should show me 2
and not 1
if I select a 07/04/2016. It should count all the policies from the start to that specific date(which is 07/04/2016)
Please try to use the following updated measure.
Measure_Update = VAR CountCurrentDay = CALCULATE ( DISTINCTCOUNT ( Table1[PolicyNumber] ), FILTER ( ALL ( Table1 ), Table1[Date] = MAX ( Td_Date[Date] ) && Table1[Job] = "Cancellation" ) ) RETURN ( IF ( CountCurrentDay > 0, CountCurrentDay, CALCULATE ( DISTINCTCOUNT ( Table1[PolicyNumber] ), FILTER ( ALL ( Table1 ), Table1[Date] < MAX ( Td_Date[Date] ) && Table1[Job] = "Cancellation" ) ) ) )Best Regards,
Herbert
