Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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 ?
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.
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 ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |