DAX: Returning information on two (or more) matching items in a single column

[Bderi]

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

[TomMartens · Accepted 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

[TomMartens · Accepted 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

[Bderi]

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! 

[TomMartens · Accepted solution]

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

[Bderi]

Thanks for your kind help, Tom!

Greetings to Hambug!