cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Help with Dax Measure for Policy Count

DateDimensionIDJobCloseDatePolicyNumberTermNumberBranchIDJobMostRecentModel
17/1/2016 0:00A11Submission1
27/2/2016 0:00A12Policy Change1
27/2/2016 0:00B12Policy Change1
37/3/2016 0:00A13Cancellation0
37/3/2016 0:00B13Cancellation0
37/3/2016 1:00A14Reinstatement1
37/3/2016 1:00B14Reinstatement1
47/5/2016 0:00A15Cancellation1

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Habib Established Member
Established Member

Re: Help with Dax Measure for Policy Count

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 15
Habib Established Member
Established Member

Re: Help with Dax Measure for Policy Count

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]))
donsvensen Member
Member

Re: Help with Dax Measure for Policy Count

Hi

 

You can add a measure with the following DAX

 

CountPolicyNumbers = DISTINCTCOUNT(TableName[PolicyNumber])

 

BR

Erik

Re: Help with Dax Measure for Policy Count

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.

Re: Help with Dax Measure for Policy Count

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

 

donsvensen Member
Member

Re: Help with Dax Measure for Policy Count

Then I might have misunderstood your datamodel..

 

my example

 

capture20160729141145760.png

 

Is the other columns in your dataset also relevant for your calculation ? - some sort of inventory calculation ?

 

br

erik

Highlighted
Habib Established Member
Established Member

Re: Help with Dax Measure for Policy Count

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.

Re: Help with Dax Measure for Policy Count

Thanks @Habib this seems working

I would like to know why we give Values on LHS of the equation

Re: Help with Dax Measure for Policy Count

Thanks @Habib this seems working

I would like to know why we give Values on LHS of the equation

Habib Established Member
Established Member

Re: Help with Dax Measure for Policy Count

It gives you the value of field in current context and in your case its the selected value in Slicer.