cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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. 

3 REPLIES 3
Microsoft
Microsoft

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.

v-jingzhang_0-1600684095508.png

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.

Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User IV
Super User IV

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

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors