Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:00 | B | 1 | 2 | Policy Change | 1 |
3 | 7/3/2016 0:00 | A | 1 | 3 | Cancellation | 0 |
3 | 7/3/2016 0:00 | B | 1 | 3 | Cancellation | 0 |
3 | 7/3/2016 1:00 | A | 1 | 4 | Reinstatement | 1 |
3 | 7/3/2016 1:00 | B | 1 | 4 | Reinstatement | 1 |
4 | 7/5/2016 0:00 | A | 1 | 5 | Cancellation | 1 |
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
Solved! Go to Solution.
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.
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
Is the other columns in your dataset also relevant for your calculation ? - some sort of inventory calculation ?
br
erik
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")
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.
lets say the order of sorting would be Primary jy which is an identity column(1,1)
Thanks @Habib this seems working
I would like to know why we give Values on LHS of the equation
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |