Creating a measure for category

[tmhalila]

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

[TheoC]

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:

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

2. 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

[tmhalila]

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.

[TheoC]

Hi @tmhalila 

I definitely didn't get that from your post haha.  See attached PBIX.  All you need to do is change the calculated column to the following:

_CompletedServices = SWITCH ( TRUE () , Services[CompletedService] = 1 , "Completed" , "Not Completed" )

Hope that helps 🙂

Theo

[tmhalila]

Sorry, I keep explaining may be because of a different industry 😀 but we are almost there.

What I need to know is how many clients

completed 1, 

completed 2,

completed 3,

completed 4,

completed 5 or more 

services.

Example 

15 - 19 clients participated in more categories of services than others.

Examples of services are:

• enrolled in a program
• attended mechanical training
• provided with mechanical tools
• attended business skills workshops
• attached to internship
• employed

So there are clients who just end at service 1 while others receive all or a few out of those mentioned.

[TheoC · Accepted solution]

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 

[tmhalila]

Finally, Solved!! 🎶 thanks a lot and more blessings, you saved my day!! I just modified it a little bit here

Service Category = 
VAR _colDistinctClientServices = 
CALCULATE ( DISTINCTCOUNT ( Services[serviceid] ) , ALLEXCEPT ( 'Services' , Services[clientid] ) , Services[CompletedService] = 1 
)

VAR _servicecategories = 
SWITCH( TRUE,
_colDistinctClientServices <1, " ",
_colDistinctClientServices =1, "Completed One",
_colDistinctClientServices =2, "Completed Two",
"completed Three or More"
)

Return  
_servicecategories 

[TheoC]

@tmhalila love your work mate! Well done!

All the best and look forward to staying in touch!

Theo 🙂