Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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/04/2016 on the slicer then the totalCount should be 2
I was able to write the dax in Dax Studio but not able to put it in tabular model.
Kindly help me out on this.
Friend
@Anonymous
I do have SSDT.
I want to know how to write the measure for anove condition.
According to your description, you want to count policies if the Job on LatestCloseDate is "Cancellation" on DateDimensionID wise. Right?
In this scenario, you can create a calcualted column to determine if current row can be counted as 1 with formula below:
Column = IF( CALCULATE(MAX(Table4[JobCloseDate]),ALLEXCEPT(Table4,Table4[PolicyNumber],Table4[DateDimensionID]))=Table4[JobCloseDate] && Table4[Job]="Cancellation",1,0)
Then you just need to create a measure to sum this column.
It can show correct result when filtered with DateDimensionID.
Regards,
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |