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, in PowerPivot, I am struggling to find information on matching values in the same column.
I would like to display the matching name in the below table, e.g. for John with ID '1' it would be Peter (also with ID '1'), for Jack (ID '2') it would be Mary (also ID '2'), etc.
I have been able to 1) find the number of occurrences using:
=COUNTX(FILTER (Table2, (EARLIER ( [ID] ) = [ID] )),Table2[Name])
...also, I was able to add up the matching values with
=SUMX(FILTER (Table2, (EARLIER ( [ID] ) = [ID] )),Table2[Name])
...but was unable to display 'Peter' in the row of 'John' and 'John' in the row of Peter. I am assuming it is possible, not sure how...any help would be much appreciated
Solved! Go to Solution.
Hey,
I would give CONCATENATEX a try, basically it's the same as your SUMX formula,
In addition to that, you can adjust the expression part to return BLANK if the current name matches the nema from the iteration, but this would also miss the chance to catch 2 different John(s).
Hopefully this gives you an idea!
Regards,
Tom
Hey,
unfortunately there is no official group of these X functions, within books these functions are grouped with some others to "table iterators".
Besides SUMX and CONCATENATEX you will find some others from the offical documentation under "statistical functions":
https://msdn.microsoft.com/en-us/query-bi/dax/statistical-functions-dax
Regards,
Tom
Hey,
I would give CONCATENATEX a try, basically it's the same as your SUMX formula,
In addition to that, you can adjust the expression part to return BLANK if the current name matches the nema from the iteration, but this would also miss the chance to catch 2 different John(s).
Hopefully this gives you an idea!
Regards,
Tom
Amazing ,Tom, this is exactly what I was looking for! Thanks very much!
Actually, before posting, I tried looking up other forms of X-formulas (SUMX, COUNTX), but did not find "CONCATENATEX" - is there a list of these somewhere? Does this group of formulas have a specific name? Thank again!
Hey,
unfortunately there is no official group of these X functions, within books these functions are grouped with some others to "table iterators".
Besides SUMX and CONCATENATEX you will find some others from the offical documentation under "statistical functions":
https://msdn.microsoft.com/en-us/query-bi/dax/statistical-functions-dax
Regards,
Tom
Thanks for your kind help, Tom!
Greetings to Hambug!
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |