I have created a calculated column that categorises services as IaaS, PaaS Hosting (Containers) or PaaS Hosting (Other Cloud Native) based on the service name:
Service Type = IF('AWS Monthly Summary'[product_servicename]="Amazon Elastic Compute Cloud", "IaaS", IF('AWS Monthly Summary'[product_servicename]="Amazon Elastic File System", "IaaS", IF('AWS Monthly Summary'[product_servicename]="Elastic Load Balancing", "IaaS", IF(CONTAINSSTRING('AWS Monthly Summary'[product_servicename], "Container"),"PaaS Hosting (Containers)", IF(CONTAINSSTRING('AWS Monthly Summary'[product_servicename], "DB"),"PaaS Hosting (Other Cloud Native)", IF(CONTAINSSTRING('AWS Monthly Summary'[product_servicename], "Simple"), "PaaS Hosting (Other Cloud Native)", IF('AWS Monthly Summary'[product_servicename]="Amazon Athena", "PaaS Hosting (Other Cloud Native)", IF('AWS Monthly Summary'[product_servicename]="Amazon ElastiCache", "PaaS Hosting (Other Cloud Native)", IF(CONTAINSSTRING('AWS Monthly Summary'[product_servicename], "Kinesis"), "PaaS Hosting (Other Cloud Native)", IF('AWS Monthly Summary'[product_servicename]="Amazon Managed Streaming for Apache Kafka", "PaaS Hosting (Other Cloud Native)", IF('AWS Monthly Summary'[product_servicename]="Amazon QuickSight", "PaaS Hosting (Other Cloud Native)", IF('AWS Monthly Summary'[product_servicename]="Amazon Redshift", "PaaS Hosting (Other Cloud Native)", IF('AWS Monthly Summary'[product_servicename]="Amazon Relational Database Service", "PaaS Hosting (Other Cloud Native)", IF('AWS Monthly Summary'[product_servicename]="Amazon S3 Glacier Deep Archive", "PaaS Hosting (Other Cloud Native)", IF('AWS Monthly Summary'[product_servicename]="Amazon SageMaker", "PaaS Hosting (Other Cloud Native)", IF('AWS Monthly Summary'[product_servicename]="Amazon WorkSpaces", "PaaS Hosting (Other Cloud Native)", IF(CONTAINSSTRING('AWS Monthly Summary'[product_servicename], "Code"), "PaaS Hosting (Other Cloud Native)", IF('AWS Monthly Summary'[product_servicename]="AWS Data Pipeline", "PaaS Hosting (Other Cloud Native)", IF('AWS Monthly Summary'[product_servicename]="AWS Device Farm", "PaaS Hosting (Other Cloud Native)", IF('AWS Monthly Summary'[product_servicename]="AWS Glue", "PaaS Hosting (Other Cloud Native)", IF('AWS Monthly Summary'[product_servicename]="AWS Lambda", "PaaS Hosting (Other Cloud Native)", IF('AWS Monthly Summary'[product_servicename]="AWS Step Functions", "PaaS Hosting (Other Cloud Native)", "Platform Enablement"))))))))))))))))))))))
I am trying to create a summary measure where: if IaaS AND not PaaS Containers and NOT PaaS Other Cloud Native, THEN "IaaS Only", If IaaS and PaaS Containers then "IaaS & PaaS Containers", If IaaS AND PaaS Other Cloud Native AND not PaaS Containers then "IaaS & PaaS (Cloud Native)", If PaaS Containers AND not IaaS then "PaaS Only (Containers)", If PaaS Other Cloud Native AND not IaaS AND not PaaS Containers then "PaaS Only (Cloud Native)". This measure will be used to produce a summary table that counts the number of services in each category for each month.
I can't find a way in Power BI to summarise a calculated column (text) into a measure. This needs to be at the measure level to count the number of services in a particular month that fall into each of those categories and not at the calculated column level because one service can only be one value (IaaS, PaaS Containers or PaaS Other Cloud Native), but I want to be able to collectively summarise these services as "IaaS Only", "IaaS & PaaS Containers", "IaaS & Paas Cloud Native", "PaaS Only Containers", and "PaaS Only Cloud Native" as per the above logic. How can I summarise the text column into a measure since Power BI won't let me create a measure from a column?
