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.

donsvensen's avatar
donsvensen
Skilled Sharer
10 years ago

Hi

 

You can add a measure with the following DAX

 

CountPolicyNumbers = DISTINCTCOUNT(TableName[PolicyNumber])

 

BR

Erik

vickyprudhvi's avatar
vickyprudhvi
Helper IV
10 years ago

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's avatar
    donsvensen
    Skilled Sharer
    10 years ago

    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

    • Habib's avatar
      Habib
      Continued Contributor
      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.

      • vickyprudhvi's avatar
        vickyprudhvi
        Helper IV
        10 years ago

        Thanks Habib this seems working

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