Forum Discussion

TodS9's avatar
TodS9
New Member
2 years ago

Grouping or goup by, DAX, Query or SQL

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.

UserOperationDescriptionSelection
User1MenuLanguageEN
User1MenuSalesParts
User1XferSMSChat

User2

MenuLanguageES
User2MenuSalesNew
User2XferLiveAgent

User3

MenuLanguageEN
User3MenuServiceReturns
User3XferLiveChat

 

DAX
Power Query

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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!

  • EylesIT's avatar
    EylesIT
    Resolver II
    2 years ago

    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?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.