Count rows where two different columns are equal

[xbommer07]

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. 

[v-jingzhang]

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.

[amitchandak]

@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])

[Greg_Deckler]

@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())