topic Counting the number of matching items in another column in DAX Commands and Tips

Counting the number of matching items in another column

Anonymous — Thu, 22 Aug 2019 22:38:27 GMT

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:

ID	Item1	Item2
1	Banana	Apple
2	Peach	Apple
3	Apple	Orange
4	Banana	Orange
5	Orange	Apple

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.

Re: Counting the number of matching items in another column

Greg_Deckler — Fri, 23 Aug 2019 04:31:38 GMT

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.

Re: Counting the number of matching items in another column

Anonymous — Fri, 23 Aug 2019 14:52:14 GMT

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 ?