COUNTROWS total rows by group

[bhalicki]

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!

[ERD · Accepted solution]

@bhalicki ,

You can try this measure:

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

[bhalicki]

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.

[bhalicki]

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

[SamInogic]

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/

[bhalicki]

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?

[SamInogic]

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/