topic Re: COUNTROWS total rows by group in DAX Commands and Tips

COUNTROWS total rows by group

bhalicki — Thu, 04 May 2023 06:31:26 GMT

I have the following tables and relationships:
User

UserId	UserName
1	Bill
2	John
3	Fred

License

LicenseId	LicenseDesc	UserId
1	License A	1
2	License B	1
3	License C	2
4	License D	2
5	License E	2
6	License F	3

I want to write a measure that counts the number of rows in the license table (based on the username grouping) and results in the following table:

LicenseId	LicenseDesc	UserName	LicensesAllocated
1	License A	Bill	2
2	License B	Bill	2
3	License C	John	3
4	License D	John	3
5	License E	John	3
6	License F	Fred	1

I assume I need to use COUNTROWS, but the combinations I have tried either end up with '1' for every value in LicenseAllocated, or a cartesian product.

Any help would be greatly appreciated!

Re: COUNTROWS total rows by group

SamInogic — Thu, 04 May 2023 07:15:29 GMT

Hi bhalicki ,

As per your requirement of getting count of Licenses per User, you should create a column in the User Table with below DAX expression.

License Allocated = CALCULATE(COUNTROWS(License), FILTER(License, License[UserId] = User[UserId]))

Add columns in the table and you can see the expected result will include the License Allocated by each user.

If this answer helps, please mark it as an Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service: http://www.inogic.com/services/

Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/

Re: COUNTROWS total rows by group

bhalicki — Thu, 04 May 2023 07:44:04 GMT

Hi SamInogic ,

Thanks for your quick reply! I tried your solution, but I get:

This is the measure (note the RELATED function):

LicenseAllocated = CALCULATE(COUNTROWS(License), FILTER(License, License[UserId] = RELATED(User[UserId])))

These are the relationships:

Users table:

License table:

Any ideas?

Re: COUNTROWS total rows by group

SamInogic — Thu, 04 May 2023 08:49:55 GMT

Hi bhalicki

It seems that you followed the steps for the solution but created measure instead of the Column in the table.

License Allocated = CALCULATE(COUNTROWS(License), FILTER(License, License[UserId] = User[UserId]))

Can you check the above DAX as a column in the table and add on the report to check output?

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service: http://www.inogic.com/services/

Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/

Re: COUNTROWS total rows by group

bhalicki — Thu, 04 May 2023 09:04:18 GMT

Yes, sorry I need a measure not a calculated column on a table.

Re: COUNTROWS total rows by group

ERD — Thu, 04 May 2023 10:10:47 GMT

bhalicki ,

You can try this measure:

LicensesAllocated = CALCULATE(COUNTROWS(License), FILTER(ALL(License), License[UserId] = SELECTEDVALUE(License[UserId])))

Re: COUNTROWS total rows by group

bhalicki — Sat, 06 May 2023 02:58:45 GMT

HI ERD , thankyou that worked perfectly!

Out of interest, do you know why it wouldn't calculate the total for this measure?

Kind regards,

Ben.