Forum Discussion
Resolver IICreating a measure for category
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
Resolver IIThanks 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.
Community ChampionHi 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
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