cancel
Showing results for 
Search instead for 
Did you mean: 
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
Resident Rockstar
Resident Rockstar

@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
Resident Rockstar
Resident Rockstar

@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
Solution Sage
Solution Sage

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
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors