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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
tmhalila
Resolver I
Resolver I

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

tmhalila_0-1659918458740.png

 

Age group table

agegroupidname
5B9FE2ED-10B6-4295-A13A-055021CA87D120-24
CD3E266B-02E7-4069-A01F-84A6C447829425-29
99A6E90F-36FB-4C67-8214-AC3AFF33176815-19
678F724B-A612-4D3F-962D-B95112CD1CFD10-14
1 ACCEPTED SOLUTION
TheoC
Community Champion
Community Champion

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:

 

TheoC_0-1659995505595.png

Power BI file attached 🙂

 

Theo 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

View solution in original post

7 REPLIES 7
TheoC
Community Champion
Community Champion

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:

 

TheoC_0-1659922828297.png

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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
Community Champion
Community Champion

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" )
TheoC_0-1659938974682.png

 

Hope that helps 🙂

 

Theo

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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 

tmhalila_0-1659918458740.png

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
Community Champion
Community Champion

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:

 

TheoC_0-1659995505595.png

Power BI file attached 🙂

 

Theo 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

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
Community Champion
Community Champion

@tmhalila love your work mate! Well done!

 

All the best and look forward to staying in touch!

 

Theo 🙂

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.