Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vickyprudhvi
Helper IV
Helper IV

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
Habib
Responsive Resident
Responsive Resident

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

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

 

capture20160729141145760.png

 

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

 

br

erik

Habib
Responsive Resident
Responsive Resident

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.

Thanks @Habib this seems working

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

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

hi @Habib

 

how to count policy which has prior Job type as Reinstatement

I am not able to firgure it out

thnak you

for help

If you are looking for count of policies against specific job type, here is the syntax.

 

PolictCountReinstatement = CALCULATE(DISTINCTCOUNT(Table1[PolicyNumber]),Table1[JobCloseDate]<=VALUES(Table1[JobCloseDate]),Table1[Job] ="Reinstatement")

@Habib
I want to count policy whose previous job status is reinstatement

What will be sorting order? If date, you can have multiple policies within same date. To find context to previous value we need to sure about the data sorting order.

@Habib

lets say the order of sorting would be Primary jy which is an identity column(1,1)

@Habib
I guess sorting Order ll be policies

Thanks @Habib this seems working

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

Habib
Responsive Resident
Responsive Resident

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.