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

Counting the number of matching items in another column

Hi!

I'm new to Power BI and I wonder if it is possible to count the number of matching elements between two columns. For instance, I have a table that looks like this:

IDItem1Item2
1BananaApple
2PeachApple
3AppleOrange
4BananaOrange
5OrangeApple

 

The problem is that I want to know how many apples there are in total, how many bananas there are in total, how many peaches there are in total, and so on. I don't know if there is a way to do that dynamically (without having to specify the item's name), and then calculate it all in the same query.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Counting the number of matching items in another column

Sure, you should be able to do this although the exact DAX code will vary depending upon what you are specifically trying to accomplish, but as a column calculation, that would look something along the lines of:

 

Column = 
  VAR __table = FILTER(ALL('Table'),[Item1] = EARLIER('Table'[Item1)))
  VAR __table1 = FILTER(ALL('Table'),[Item2] = EARLIER('Table'[Item1))
  RETURN
    COUNTROWS(__table) + COUNTROWS (__table1)

Something along those lines. The above code should give you the total count of the current value of Item1 in your row for both Item1 and Item2 columns assuming I didn't miss a paren or something because I didn't test it.


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

@ 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!




View solution in original post

Highlighted
Frequent Visitor

Re: Counting the number of matching items in another column

Actually, I modified the query a bit by adding the column as well.

Column = 
  VAR __table = FILTER(ALL('Table'[Item1]),[Item1] = EARLIER('Table'[Item1]))
  VAR __table1 = FILTER(ALL('Table'[Item2]),[Item2] = EARLIER('Table'[Item1]))
  RETURN
    COUNTROWS(__table) + COUNTROWS (__table1)

 
Thanks for that, it helped!
Though I've got another question. Is there a way to merge the tables together and calculate a distinct count ?

View solution in original post

2 REPLIES 2
Highlighted
Super User IX
Super User IX

Re: Counting the number of matching items in another column

Sure, you should be able to do this although the exact DAX code will vary depending upon what you are specifically trying to accomplish, but as a column calculation, that would look something along the lines of:

 

Column = 
  VAR __table = FILTER(ALL('Table'),[Item1] = EARLIER('Table'[Item1)))
  VAR __table1 = FILTER(ALL('Table'),[Item2] = EARLIER('Table'[Item1))
  RETURN
    COUNTROWS(__table) + COUNTROWS (__table1)

Something along those lines. The above code should give you the total count of the current value of Item1 in your row for both Item1 and Item2 columns assuming I didn't miss a paren or something because I didn't test it.


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

@ 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!




View solution in original post

Highlighted
Frequent Visitor

Re: Counting the number of matching items in another column

Actually, I modified the query a bit by adding the column as well.

Column = 
  VAR __table = FILTER(ALL('Table'[Item1]),[Item1] = EARLIER('Table'[Item1]))
  VAR __table1 = FILTER(ALL('Table'[Item2]),[Item2] = EARLIER('Table'[Item1]))
  RETURN
    COUNTROWS(__table) + COUNTROWS (__table1)

 
Thanks for that, it helped!
Though I've got another question. Is there a way to merge the tables together and calculate a distinct count ?

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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