topic Re: Grouping or goup by, DAX, Query or SQL in DAX Commands and Tips

Grouping or goup by, DAX, Query or SQL

TodS9 — Wed, 25 Oct 2023 17:32:20 GMT

Thank you ahead of time. Below is a simplified table where I want to count how many EN users selected Chat.

I was trying to figure out how to group the information of the User and count if both EN and Chat were in the grouping but I don't have the knowledge how to do it.

User	Operation	Description	Selection
User1	Menu	Language	EN
User1	Menu	Sales	Parts
User1	Xfer	SMS	Chat
User2	Menu	Language	ES
User2	Menu	Sales	New
User2	Xfer	Live	Agent
User3	Menu	Language	EN
User3	Menu	Service	Returns
User3	Xfer	Live	Chat

Re: Grouping or goup by, DAX, Query or SQL

Greg_Deckler — Wed, 25 Oct 2023 18:13:49 GMT

TodS9 Try:

Measure = VAR __Table1 = SELECTCOLUMNS( FILTER( 'Table', [Selection] = "EN" ), "User", [User]) VAR __Table2 = SELECTCOLUMNS( FILTER( 'Table', [Selection] = "Chat" ), "User", [User]) VAR __Result = COUNTROWS(INTERSECT( __Table1, __Table2 ) ) RETURN __Result

Re: Grouping or goup by, DAX, Query or SQL

EylesIT — Wed, 25 Oct 2023 18:15:45 GMT

TodS9, here is my suggested solution.

Create a measure called [Has EN and Chat] with this DAX formula:

Has EN and Chat = SUMX( VALUES(YourTable[User]), VAR hasEN = CALCULATE(DISTINCTCOUNT(YourTable[User]), YourTable[Selection] = "EN") VAR hasChat = CALCULATE(DISTINCTCOUNT(YourTable[User]),YourTable[Selection] = "Chat") RETURN IF(hasEN > 0 && hasChat > 0, 1) )

Add a table visual to your report and drag the [User] column and the [Has EN and Chat] mesaure. This gives the output below:

Is this what you are looking for?

Re: Grouping or goup by, DAX, Query or SQL

Anonymous — Wed, 25 Oct 2023 19:14:48 GMT

Thanks EylesIT , I was able to get the data needed. It would have been weeks if ever for me to figure this out. There's another column I want to show as the rows and not the "User" but I'll work on that. Awesome answer.

Re: Grouping or goup by, DAX, Query or SQL

Anonymous — Wed, 25 Oct 2023 19:19:37 GMT

Thanks Greg_Deckler . This answer may allow me to get the information in the layout I eventually want. It's just going to take some extra time to learn and understand how it works. You guys are fantastic. I've been staring at this for a couple of weeks before breaking down and asking for help. Thank you!