Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello BI Team,
I have read a number of posts on this topic however I cannot find one that is just right.
I have a table, the data source is SQL. The table name is Call Log.
Columns are Date, Time, Phone Number, Result. A bunch of others but these are the important ones.
Date | Time | Phone Number | Result |
9/15/2020 | 05:21 AM | 555-555-5555 | 3 (3 = Missed) |
9/15/2020 | 05:45 AM | 444-444-4444 | 0 (0 = Transferred) |
9/15/2020 | 05:55 AM | 555-555-5555 | 1 (1 = Answered) |
The result column tells me what happened during the call. For example if some called and then hung up before we answered the call the status would be = 3.
I need to count the calls that occured on the same day (disregarding time) by the same phone number by status.
For all calls with the result of 3, I want to count the number of records on the same day that has the same phone number.
Hi @xbommer07 , you can try a measure like this:
Count = COUNTROWS(FILTER('Call Log','Call Log'[Date] = MAX('Call Log'[Date]) && 'Call Log'[Phone Number] = MAX('Call Log'[Phone Number])))
In addition, if you want to show the count value in a visual like table or matrix, you can try an easier measure like:
Count of Call = COUNT('Call Log'[Result])
And drag it into the visual’s Values, you will also see the count result calculated automatically and correctly.
Best Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
@xbommer07 , A measure for 3 and 1 happened on the same day by the same number
countx( filter(summarize(Table, Table[Date],Table[Phone Number],
"_1" , countx(filter(Table,Table[Result] =3),Table[Phone Number]),
"_2" , countx(filter(Table,Table[Result] =1),Table[Phone Number])
),not(isblank([_1])) && not(isblank([_1]))),[Phone Number])
@xbommer07 You could create a column or measure like this:
Column =
VAR __Table = FILTER('Table (20)',[Date]=EARLIER([Date]) && [Phone Number]=EARLIER([Phone Number]))
VAR __MaxResult = MAXX(__Table,[Result])
RETURN
IF(__MaxResult=3,COUNTROWS(__Table),BLANK())
Measure =
VAR __Phone = MAX([Phone Number]
VAR __Date = MAX([Date])
VAR __Table = FILTER('Table (20)',[Date]=__Date && [Phone Number]=__Phone ))
VAR __MaxResult = MAXX(__Table,[Result])
RETURN
IF(__MaxResult=3,COUNTROWS(__Table),BLANK())
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |