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 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
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  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] )
            )
        )
)
    • Revathi_Kannan's avatar
      Revathi_Kannan
      Helper 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.

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        4 years ago

        Hi,

        Please check the attached file.

        Thanks.