cancel
Showing results 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

Helper I

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

2 ACCEPTED SOLUTIONS
Super User

Hi,

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

It is for creating a new table.

``````New Table =
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.

Super User

Hi,

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.

9 REPLIES 9
Super User

Hi,

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

It is for creating a new table.

``````New Table =
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.

Helper I

Super User

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

Helper I

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

Super User

Hi,

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.

Helper I

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

Helper I

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

Helper I

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

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?