I have the following tables and relationships:UserUserIdUserName1Bill2John3Fred LicenseLicenseIdLicenseDescUserId1License A12License B13License C24License D25License E26License F3 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:LicenseIdLicenseDescUserNameLicensesAllocated1License ABill22License BBill23License CJohn34License DJohn35License EJohn36License FFred1I 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!

bhalicki ,You can try this measure:LicensesAllocated = CALCULATE(COUNTROWS(License), FILTER(ALL(License), License[UserId] = SELECTEDVALUE(License[UserId])))

COUNTROWS total rows by group | Microsoft Fabric Community

6 Replies

SamInogic
Super User
3 years ago
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 [email protected]
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
- bhalicki
  Helper V
  3 years ago
  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
    Super User
    3 years ago
    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 [email protected]
    Service: http://www.inogic.com/services/
    Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
ERD
Community Champion
3 years ago
bhalicki ,
You can try this measure:
LicensesAllocated = CALCULATE(COUNTROWS(License), FILTER(ALL(License), License[UserId] = SELECTEDVALUE(License[UserId])))
- bhalicki
  Helper V
  3 years ago
  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
Helper V
3 years ago
Yes, sorry I need a measure not a calculated column on a table.