Forum Discussion
Creating a measure for category
Hello,
I have a dataset with two tables, one for the age group and the other for services completed. I want to create a visual by category of the age group for those who completed one service, two services, and more like in the chart below. I was thinking about the Count and switch function but have failed to go about it. Please assist
Service dataset attached: ServiceTable
Expected chart
Age group table
| agegroupid | name |
| 5B9FE2ED-10B6-4295-A13A-055021CA87D1 | 20-24 |
| CD3E266B-02E7-4069-A01F-84A6C4478294 | 25-29 |
| 99A6E90F-36FB-4C67-8214-AC3AFF331768 | 15-19 |
| 678F724B-A612-4D3F-962D-B95112CD1CFD | 10-14 |
Hi tmhalila
Haha - alright, that sounds good. You can use the following to create a calculated column:
_colDistinctClientServices =
CALCULATE (
DISTINCTCOUNT ( Services[serviceid] ) ,
ALLEXCEPT ( 'Services' , Services[clientid] ) , Services[_CompletedServices] = "Completed"
)Once you have added the column, you can then drag it onto the visual as the "Legend". This will give you the following:
Power BI file attached 🙂
Theo
7 Replies
Hi tmhalila
I've put together the attached PBIX which hopefully will assist. I recreated your model with an "Age Group" table just to make sure there are similarities (grouped by AgeGroupID). Output as per below:
I basically did two things:
- Measure for Distinct Count on Completed Services given the way in which this was created / calculated is unknown but did have unique figures:
_distinctcount = DISTINCTCOUNT ( 'Services'[CompletedService] )
- Calculated column using:
_CompletedServices = CALCULATE ( [_distinctcount] , ALLEXCEPT ( 'Services' , 'Services'[ClientID] ) )
From here, I just dragged the various fields into the respective visual.
Hoping it's what you're after.
Thanks heaps,
Theo
Thanks for the quick feedback, this one provides different results.
The Column CompletedService gives us the status of clients who completed services, those with 1 completed and those with 0 not completed.
Hence, we expect to have clients who completed multiple services, those who completed one, and others who completed zero. From the serviceID we have different types of IDs meaning different services of which some clients completed many while others complete none.
Sorry, I think I missed explaining clearly in the first post.
- Measure for Distinct Count on Completed Services given the way in which this was created / calculated is unknown but did have unique figures:
