Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bhalicki
Helper II
Helper II

COUNTROWS total rows by group

I have the following tables and relationships:
User

UserIdUserName
1Bill
2John
3Fred

 

License

LicenseIdLicenseDescUserId
1License A1
2License B1
3License C2
4License D2
5License E2
6License 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:

LicenseIdLicenseDescUserNameLicensesAllocated
1License ABill2
2License BBill2
3License CJohn3
4License DJohn3
5License EJohn3
6License FFred1


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!

1 ACCEPTED SOLUTION
ERD
Super User
Super User

@bhalicki ,

You can try this measure:

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

ERD_0-1683195041361.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

6 REPLIES 6
ERD
Super User
Super User

@bhalicki ,

You can try this measure:

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

ERD_0-1683195041361.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

HI @ERD , thankyou that worked perfectly!

Out of interest, do you know why it wouldn't calculate the total for this measure?
bhalicki_0-1683341911582.png


Kind regards,

Ben.

bhalicki
Helper II
Helper II

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

SamInogic
Super User
Super User

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

SamInogic_0-1683184467432.png


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

SamInogic_1-1683184484454.png

 

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/

Hi @SamInogic ,

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

bhalicki_0-1683186060990.png

This is the measure (note the RELATED function):

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

These are the relationships:
bhalicki_1-1683186140314.png
Users table:
bhalicki_3-1683186180716.png

License table:

bhalicki_4-1683186213303.png

Any ideas?


 

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/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors