Forum Discussion
Resources for basic Dax Group By Concatenating Values
Does anyone have any resources or guides for how to achieve the equivalent to M Query's Group By in DAX with turning a secondary column into a list of values? I'm not seeing how to do that in guides like https://dax.guide/groupby/
The use case is a table that might look like this
| Accessed From | |
| [email protected] | Seattle, WA |
| [email protected] | Los Angeles, CA |
| [email protected] | San Diego, CA |
| [email protected] | Boston, MA |
The idea would be to SUMMARIZE () the table so that the summary is grouped by Email:
| Accessed From | |
| [email protected] | Seattle, WA|Los Angeles, CA|San Diego, CA |
| [email protected] | Boston, MA |
Any suggestions? I'd think it's groupby then a concatenatex of some form?
Thank you
you can try something like:
Table = ADDCOLUMNS(VALUES([email]), "Accessed From", CONCATENATEX(Table, [Accessed From], "|"))As a calculated table:
Table2 = SUMMARIZE ( 'Table', 'Table'[Email], "Accessed From", CONCATENATEX ( VALUES ( 'Table'[Accessed From] ), 'Table'[Accessed From], " | " ) )Update - this code fixed the problem
a_usacities_sum =ADDCOLUMNS(DISTINCT(SUMMARIZE(a_uscities, a_uscities[city], a_uscities[Count Dupes])),"States",VAR _city = a_uscities[city]RETURNCALCULATE(CONCATENATEX(VALUES ( 'a_uscities'[state_id] ),'a_uscities'[state_id]," | "),a_uscities[city] = _city))
4 Replies
you can try something like:
Table = ADDCOLUMNS(VALUES([email]), "Accessed From", CONCATENATEX(Table, [Accessed From], "|"))As a calculated table:
Table2 = SUMMARIZE ( 'Table', 'Table'[Email], "Accessed From", CONCATENATEX ( VALUES ( 'Table'[Accessed From] ), 'Table'[Accessed From], " | " ) )Uh oh. I am actually losing row context trying this with a geo table where I am trying to take the 107,000 incorporated towns in the US and concatenatex the names of the states where there are dupes
a_usacities_sum = ADDCOLUMNS( DISTINCT(SUMMARIZE(a_uscities, a_uscities[city], a_uscities[Count Dupes])), "States", CONCATENATEX(VALUES ( 'a_uscities'[state_id] ), 'a_uscities'[state_id], " | " ))The above is missing row context
Going to try some some filter code changes
Update - this code fixed the problem
a_usacities_sum =ADDCOLUMNS(DISTINCT(SUMMARIZE(a_uscities, a_uscities[city], a_uscities[Count Dupes])),"States",VAR _city = a_uscities[city]RETURNCALCULATE(CONCATENATEX(VALUES ( 'a_uscities'[state_id] ),'a_uscities'[state_id]," | "),a_uscities[city] = _city))