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
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.

View solution in original post

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

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.

View solution in original post

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

Highlighted
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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 217 members 2,427 guests
Please welcome our newest community members: