topic Re: COUNTROWS WITH MULTIPLE COLUMNS in DAX Commands and Tips

COUNTROWS WITH MULTIPLE COLUMNS

Revathi_Kannan — Fri, 29 Apr 2022 08:16:49 GMT

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.

Re: COUNTROWS WITH MULTIPLE COLUMNS

SpartaBI — Fri, 29 Apr 2022 08:43:39 GMT

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?

Re: COUNTROWS WITH MULTIPLE COLUMNS

Revathi_Kannan — Fri, 29 Apr 2022 08:48:24 GMT

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

Re: COUNTROWS WITH MULTIPLE COLUMNS

Jihwan_Kim — Fri, 29 Apr 2022 08:51:17 GMT

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

Re: COUNTROWS WITH MULTIPLE COLUMNS

Revathi_Kannan — Fri, 29 Apr 2022 09:03:37 GMT

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 ?

Re: COUNTROWS WITH MULTIPLE COLUMNS

Revathi_Kannan — Fri, 29 Apr 2022 10:49:44 GMT

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?

Re: COUNTROWS WITH MULTIPLE COLUMNS

Revathi_Kannan — Fri, 29 Apr 2022 12:36:14 GMT

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

Re: COUNTROWS WITH MULTIPLE COLUMNS

Jihwan_Kim — Fri, 29 Apr 2022 14:18:55 GMT

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

Re: COUNTROWS WITH MULTIPLE COLUMNS

Revathi_Kannan — Fri, 29 Apr 2022 16:21:06 GMT

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.

Re: COUNTROWS WITH MULTIPLE COLUMNS

Jihwan_Kim — Sat, 30 Apr 2022 06:22:14 GMT

Hi,

Please check the attached file.

Thanks.