DAX HELP?

[vikashkarra]

Hello - trying to see if this is possible to do:

Date                          Category                 Minutes

7/1/2016                    Lobby                     2

7/1/2016                    Bar                          1

7/3/2016                    Restaurant              3

7/2/2016                    Front Desk              2

7/2/2016                    Lobby                      10

What I'd like is to have a new table that basically has this:

Date                  Category               Minutes

7/1/2016            Lobby, Bar, Gym         5

7/2/2016            Front Desk, Lobby       12

Is this something that can be done?

[vikashkarra · Accepted solution]

Measure = CONCATENATEX ( VALUES ( Data[Place Name (standardized)]), Data[Place Name (standardized)], ", " )

looks like I got it - validating as we speak, but I think that calculated measure gives me what I want -

Thanks!!

[vikashkarra]

Date                          Category                 Minutes

7/2/2016                    Gym                       2

7/1/2016                    Lobby                     2
7/1/2016                    Bar                          1
7/3/2016                    Restaurant              3
7/2/2016                    Front Desk              2
7/2/2016                    Lobby                      10

Sorry, that is the table that I'm trying to use DAX to derive:

Date                  Category               Minutes
7/1/2016            Lobby, Bar, Gym         5
7/2/2016            Front Desk, Lobby       12

[KGrice]

Hi @vikashkarra. It's difficult to tell from your sample what you're wanting to do. It looks like you're creating groups on your categories, but for 7/1 in your end table it contains Lobby, and so does 7/2 in a different group.

If you're trying to show every category that appeared on that day, the Gym doesn't show up on 7/1 in your first table, but does in your second. Should Gym be on the 7/2 line, and Restaurant be on a 7/3 line by itself?

[vikashkarra]

Date                          Category                 Minutes
7/2/2016                    Gym                       2
7/1/2016                    Lobby                     2
7/1/2016                    Bar                          1
7/3/2016                    Restaurant              3
7/2/2016                    Front Desk              2
7/2/2016                    Lobby                      10
 
Sorry, that is the table that I'm trying to use DAX to derive:
 
Date                  Category                          Minutes
7/1/2016            Lobby, Bar                       3

7/2/2016          Gym, Front Desk, Lobby   14

7/3/2016          Restaurant                          3

Sorry KGrice - this is what the desired output is!

[vikashkarra · Accepted solution]

Measure = CONCATENATEX ( VALUES ( Data[Place Name (standardized)]), Data[Place Name (standardized)], ", " )

looks like I got it - validating as we speak, but I think that calculated measure gives me what I want -

Thanks!!

[KGrice · Accepted solution]

Ha, glad you found it! I was just coming back to post something similar to create the table:

New Table = SUMMARIZE(
	TableName, 
	TableName[Date], 
	"Category List", CONCATENATEX(TableName, TableName[Category], ", "), 
	"Sum Minutes", SUM(TableName[Minutes])
	)