Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

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.

example.PNG

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

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] )
            )
        )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

Hi,

Please check the attached file.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


View solution in original post

9 REPLIES 9
Jihwan_Kim
Super User
Super User

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] )
            )
        )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


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

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] )
            )
        )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


@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.

Hi,

Please check the attached file.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


@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?

@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 ?

@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.. 

SpartaBI
Community Champion
Community Champion

@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?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.