Forum Discussion
Help with Dax Measure for Policy Count
| DateDimensionID | JobCloseDate | PolicyNumber | TermNumber | BranchID | Job | MostRecentModel |
| 1 | 7/1/2016 0:00 | A | 1 | 1 | Submission | 1 |
| 2 | 7/2/2016 0:00 | A | 1 | 2 | Policy Change | 1 |
| 2 | 7/2/2016 0:00 | B | 1 | 2 | Policy Change | 1 |
| 3 | 7/3/2016 0:00 | A | 1 | 3 | Cancellation | 0 |
| 3 | 7/3/2016 0:00 | B | 1 | 3 | Cancellation | 0 |
| 3 | 7/3/2016 1:00 | A | 1 | 4 | Reinstatement | 1 |
| 3 | 7/3/2016 1:00 | B | 1 | 4 | Reinstatement | 1 |
| 4 | 7/5/2016 0:00 | A | 1 | 5 | Cancellation | 1 |
Hi,
Above is the sample table I am working on. I looking to Calculate PolicyCount based of JobCloseDate
Example
JobcloseDate =7/5/2016 0:00 then it should show 2 policies
I am not able to write exact measure to reflex this on Power BI Desktop.
Kindly help
Hi vickyprudhvi,
Can you please give a try to below formual to add a measure.
JobCount= CALCULATE(DISTINCTCOUNT(Table1[PolicyNumber]),Table1[JobCloseDate]<=VALUES(Table1[JobCloseDate]))
You can show this on your card visual and it will give you count of distinct policy numbers till your selected date.
15 Replies
You can use DAX table to achieve this.
Create a new table by clicking on Modeling --> New Table adn add following formula.
SummaryTable = SUMMARIZE(Table1,Table1[JobCloseDate],"JobCount",DISTINCTCOUNT(Table1[PolicyNumber]))
Thank you Habib for ur reply
but I am not looking to create a new table and I want it to be dynamic as we click JobClosedate slicer on Power BI Desktop.
Hi
You can add a measure with the following DAX
CountPolicyNumbers = DISTINCTCOUNT(TableName[PolicyNumber])
BR
Erik
Thank you donsvensen for ur reply
I have a JobcloseDate as a Slicer on my Power Bi Desktop so DistnctCount as of that date would be only 1.
They way Date Slicer works in Power Bi Desktop is "Asofdate" . I am looking to show all the Policies as of tat date and not what Policy on that day
Then I might have misunderstood your datamodel..
my example
Is the other columns in your dataset also relevant for your calculation ? - some sort of inventory calculation ?
br
erik
