Forum Discussion

vickyprudhvi's avatar
vickyprudhvi
Helper IV
10 years ago
Solved

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:...
Need Help
  • Habib's avatar
    Habib
    10 years ago

    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.

Habib's avatar
Habib
Continued Contributor
10 years ago

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")
vickyprudhvi's avatar
vickyprudhvi
Helper IV
10 years ago
Habib
I want to count policy whose previous job status is reinstatement
  • Habib's avatar
    Habib
    Continued Contributor
    10 years ago

    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.

  • vickyprudhvi's avatar
    vickyprudhvi
    Helper IV
    10 years ago
    Habib
    I guess sorting Order ll be policies
  • vickyprudhvi's avatar
    vickyprudhvi
    Helper IV
    10 years ago

    Habib

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