Using groupby or summarizecolumns with a text field

[DataDiva]

I have a very simple table:

UserID      Hosp

A               1A

A               2A

B               3A

C               4A

C              5A

C               1A

I am trying in DAX, not Power Query (becuase I need to get RLS working on this table) to get a result that looks like this:

UserID          Hosp

A                  1A, 2A

B                  3A

C                   4A, 5A, 1A

I don't care about the order the values are in, I just need them to concatenate into a single field  The problem for me is that every example I can find for SUMMARIZECOLUMNS or GROUPBY is using numeric fields, so they just use SUMX or MIN, etc. I've tried using CONCATENATEX, but I get an error that says "Function "GROUPBY" scalar expressions have to be Aggregation functions over CurrentGroup()" But current group can only be used with a handful of numeric functions. 

This was my attempt:

User hospital column = GROUPBY(DISTINCT('user hospitals'[UserID]),'user hospitals'[UserID],"HospConCat",CONCATENATEX('user hospitals'[Hosp],[Hosp],","))

It's so simply to do in PowerQuery, but I can't get it to work in DAX. I'd love some help here! Thanks. 

[v-frfei-msft · Accepted solution]

Hi @DataDiva ,

To create a calculated table as below.

GROUP = SUMMARIZE('Table','Table'[UserID],"HOSP",CONCATENATEX('Table','Table'[Hosp],","))

Pbix as attached,

[DataDiva]

Thank you. The provided DAX does make a table with the first stage of the desired output. The problem is still that RLS doesn't seem to affect this table. I think I have a separate problem, with a major bug in how Power BI applies RLS. 

Thanks for helping me solve what I thought was my problem!

[MariaP]

Hi @DataDiva ,

Have you tried the Add New Measure - > Quick Measure - > Concatenated List of Values funtionality? The auto-generated code is shown here. If you add this against your UserID you will see the results needed.

List of Hospital values = 
VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('Table1'[Hospital])
VAR __MAX_VALUES_TO_SHOW = DISTINCTCOUNT('Table1'[Hospital])
RETURN
	IF(
		__DISTINCT_VALUES_COUNT > __MAX_VALUES_TO_SHOW,
		CONCATENATE(
			CONCATENATEX(
				TOPN(
					__MAX_VALUES_TO_SHOW,
					VALUES('Table1'[Hospital]),
					'Table1'[Hospital],
					ASC
				),
				'Table1'[Hospital],
				", ",
				'Table1'[Hospital],
				ASC
			),
			", etc."
		),
		CONCATENATEX(
			VALUES('Table1'[Hospital]),
			'Table1'[Hospital],
			", ",
			'Table1'[Hospital],
			ASC
		)
	)

Also, this video from Patrick (Guy In a Cube) may help with RSL information:

GuyInACube RSL Video

Hope this helps,
Maria

[DataDiva]

A quick update: I've been messing around with just using concatenatex, but it seemingly ignores my role-level security?? My table that is filtered by RLS looks like this prior to RLS being applied:

UserID     Hosp

Bob          A

Ted           B

Sally         C

Sally         D

When I view as Sally, the table looks right, showing just Sally:

UserID     Hosp

Sally         C

Sally         D

But if I concatenate Hosp, the resulting measure shows me: A,B,C,D

WTF? How can that be?