Forum Discussion

xbommer07's avatar
xbommer07
Frequent Visitor
5 years ago

Count rows where two different columns are equal

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. 

DateTimePhone NumberResult
9/15/202005:21 AM555-555-55553 (3 = Missed)
9/15/202005:45 AM444-444-44440 (0 = Transferred)
9/15/202005:55 AM555-555-55551 (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. 

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

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

     

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    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.