Forum Discussion

Revathi_Kannan's avatar
Revathi_Kannan
Helper I
4 years ago
Solved

COUNTROWS WITH MULTIPLE COLUMNS

Hi all,

 

I am looking for the solution for the past 2 weeks but i am unable to find the DAX functions which i can do it in excel. 

Please refer the below image.. I have 2 columns but my result has to be group based on Category.. I am not able to do it in DAX function. Kindly assist me.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below DAX formula and the attached pbix file.

    It is for creating a new table.

     

    New Table = 
ADDCOLUMNS (
    VALUES ( Data[Employee] ),
    "@CategorySummary",
        CALCULATE (
            IF (
                COUNTROWS ( VALUES ( Data[Category] ) ) = 2,
                "BothCategory",
                VALUES ( Data[Category] )
            )
        )
)

9 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below DAX formula and the attached pbix file.

    It is for creating a new table.

     

    New Table = 
ADDCOLUMNS (
    VALUES ( Data[Employee] ),
    "@CategorySummary",
        CALCULATE (
            IF (
                COUNTROWS ( VALUES ( Data[Category] ) ) = 2,
                "BothCategory",
                VALUES ( Data[Category] )
            )
        )
)
    • Revathi_Kannan's avatar
      Revathi_Kannan
      Helper I
      4 years ago

      Jihwan_Kim  Thank you so much for you reply.. It works in small data.. i have 85k records so i cannot create a table and paste the details manually for all 85k records. Please correct me if i am wrong.. also it is live data it needs to be refreshed automatically for SQL. So if i create the table for this measure, will it work on the Live records ?

    • Revathi_Kannan's avatar
      Revathi_Kannan
      Helper I
      4 years ago

      Jihwan_Kim  Thank you so much.. It really worked well.. Can you please let me know how to add one more column to the New table?

    • Revathi_Kannan's avatar
      Revathi_Kannan
      Helper I
      4 years ago

      Jihwan_Kim  I need to add specific text as "Team A" and "Team B" instead of 2. Please help me to modify it accordingly. 

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Hi, 

        I am not sure if I understood your question correctly, but please try the below measure in order to create a new table.

         

        New Table = 
ADDCOLUMNS (
    VALUES ( Data[Employee] ),
    "@CategorySummary",
        CALCULATE (
            IF (
                CONTAINSROW (
                    SUMMARIZE (
                        FILTER (
                            Data,
                            Data[Employee] = VAR currentemployee = Data[Employee] RETURN currentemployee
                        ),
                        Data[Category]
                    ),
                    "TeamA"
                )
                    && CONTAINSROW (
                        SUMMARIZE (
                            FILTER (
                                Data,
                                Data[Employee] = VAR currentemployee = Data[Employee] RETURN currentemployee
                            ),
                            Data[Category]
                        ),
                        "TeamB"
                    ),
                "BothCategory",
                VALUES ( Data[Category] )
            )
        )
)
  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    Revathi_Kannan can you be more specific what you are showing and where do you need this result?
    1. Is the left table a model table. 
    2. Do you need the right table result as a calculated table? as a visual table?

  • Revathi_Kannan's avatar
    Revathi_Kannan
    Helper I
    4 years ago

    SpartaBI  Thanks for the reply. Yes left is my data table in power bi. I need the total no. of count for Team A, Team B and both category based on the Employees. I am just trying to create the measure so that i will show the result on my chart. Yes, right table is how my result wants to be..