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.
Hi everyone,
Have been struggeling with this for a while so I have to ask if someone can help me. Been going through many forum threads but I can't get anything to work.
I need to count the occurences of a phone number in my data set 14 days back and 14 days forwards. This is because we operate with an assumption that we have solved our customers problem the first time of contact (first call resolution) if the telephone number does not call within a two week interval (thus i need to check 14 days back and forwards).
By now I have this calculated column:
Count = VAR TwoWeeksFromDate = DATEADD(Table[Date];14;DAY) VAR TwoWeeksbefore = DATEADD(Table[Date];-14;DAY) RETURN CALCULATE ( COUNTROWS (Table); FILTER ( ALLEXCEPT (Table; Table[PhoneNumber]); Table[Date] <= TwoWeeksFromDate && Table[Date] >= TwoWeeksbefore))
But it does just work for some of my rows, other rows just show nothing! If I change TwoWeeksFromDate to CurrentDate it works, but then it will not count correctly.
Does anyone have any tips or something that can help me out?
Thanks
Solved! Go to Solution.
Try this and let me know if it works:
Count = VAR TwoWeeksafter = [Date] + 14 VAR TwoWeeksbefore = [Date] - 14 VAR PHtoCheck = [PhoneNumber] RETURN CALCULATE ( COUNTROWS (Table); ALL (Table); Table[PhoneNumber] = PHtoCheck; Table[Date] <= TwoWeeksFromDate; Table[Date] >= TwoWeeksbefore; )
Try this and let me know if it works:
Count = VAR TwoWeeksafter = [Date] + 14 VAR TwoWeeksbefore = [Date] - 14 VAR PHtoCheck = [PhoneNumber] RETURN CALCULATE ( COUNTROWS (Table); ALL (Table); Table[PhoneNumber] = PHtoCheck; Table[Date] <= TwoWeeksFromDate; Table[Date] >= TwoWeeksbefore; )
Thank you @Anonymous!!
This worked out perfectly. Been struggeling for so long, thanks a lot.
Hi @_Kristina_
Could you show me how your dataset look like?
For example, columns "date","phone number", what you need is to calculate the rows of each number which doesn't exsit within a two week interval for twice times.
I wonder whether my understanding of your dataset and calculating rule are right, so I would like to hear back from you.
Best Regards
Maggie
Thanks, Maggie @v-juanli-msft. @Anonymous provided a nice solution, but thanks for wanting to help 🙂
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |