Forum Discussion

Revathi_Kannan's avatar
Revathi_Kannan
Icon for Helper I rankHelper 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 ha...
  • 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] )
            )
        )
)
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper 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
    Icon for Helper I rankHelper 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
    Icon for Helper I rankHelper 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
    Icon for Helper I rankHelper 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
      Icon for Super User rankSuper 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] )
            )
        )
)
      • Revathi_Kannan's avatar
        Revathi_Kannan
        Icon for Helper I rankHelper I
        4 years ago

        Jihwan_Kim I am getting an error " A table of multiplied values was supplied where a single value was expected" Can you please share your pbix tested file with the recent dax method.