Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
xbommer07
Frequent 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
v-jingzhang
Community Support
Community Support

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.

amitchandak
Super User
Super User

@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
Super User
Super User

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.