Forum Discussion
Unable to use conditional formating on Switch measure
- 1 month ago
Hi masplin
based on what i see , FORMAT function is likely the issue.since format function converts every value to text thats why you are not able to see value options.
try moving your formatting to dynamic string.
Selected Measure Value = SWITCH( SELECTEDVALUE('Scorecard'[KEY RESULT]), "# Enquiries", [Enquiry Count], "# Qualified Enquiries", [Qualified Enquiry Count], "# SQLs", [SQL Count], "# Assessments Completed", [Assessments Taking Place], "# Registrations", [Registration Fee Paid Count], "% SQL → Assessment", [RAW SQL-Assessment Converion %], "% Assessment → Start", [RAW Assess-Start Conversion %], "% SQL → Client Start (CSA)", [RAW SQL-Start Conversion %], "% SQL → Registration", [RAW SQL-Registration Converion %], "% Registration → Start", [RAW Registration-Start Conversion %], "% SQL → Start", [RAW SQL-Start Conversion %], "# Monthly FTEs (calculated)", [Live-In FTE Equiv Billable Hours ex Canc], "# Total FTE Starts", [FTEs Starting in Period], "# Ongoing FTE Starts", [FTE Starts Ongoing], "# Respite FTE Starts", [FTE Starts Respite], "# Ongoing FTE Losses", [FTE Losses Ongoing], "# Respite FTE Losses", [FTE Losses Respite], "# Net FTE Change", [Core FTE Change], "# Billable Hours Monthly", [Billable Hours], "# Billable Hours Weekly Average", [Billable Hours per Week], "# Paying Clients", [Distinct Paying Clients], "# Average Hours per Client", [Avg Billable Hours per Client], "# Hours Gained", [Client VC Weekly Hours Starting in Period], "# Hours Lost", [Client VC Weekly Hours Terminating in Period], "# Net change (hours )", [Client VC Weekly Hours Net], "# Starts (Clients)", [Clients Starting in Period], "# Restarts (Clients)", [Restarts Not Paying Previous Months, Paying Current Month and Not New Start], "# Losses (Clients)", [Clients Terminating in Period], "# Net change (clients )", [Client Net Gain in Period], "# Total Paying Clients", [Distinct Paying Clients], "# Starts", [Clients Starting in Period], "£ CPSQL", [CPSQL], "£ CPA", [CPA], "# Client Losses (exc Pauses)", [Clients Terminating in Period], "% Client churn", [Client Churn %], "% RIP Client churn", [Client Churn RIP %], "# Carers Available", [Carers Active End Period], "# Carers in placement", [Carers in Placement], "% Carer Pool Utilised", [Carer Pool Utilisation %], "# Carers Started", [Carers Starting in Period], "# Carers Lost", [Carers Terminating in Period], "# Net Carer change", [Carer Net Change], "# Hired", [Carers Registering in Period], "% Carer churn", [Carer Churn] )Use below in dynamic string format.
SWITCH( SELECTEDVALUE('Scorecard'[KEY RESULT]), "# Enquiries", "#,##0", "# Qualified Enquiries", "#,##0", "# SQLs", "#,##0", "# Assessments Completed", "#,##0", "# Registrations", "#,##0", "% SQL → Assessment", "0.0%", "% Assessment → Start", "0.0%", "% SQL → Client Start (CSA)", "0.0%", "% SQL → Registration", "0.0%", "% Registration → Start", "0.0%", "% SQL → Start", "0.0%", "# Monthly FTEs (calculated)", "#,##0.0", "# Total FTE Starts", "#,##0", "# Ongoing FTE Starts", "#,##0", "# Respite FTE Starts", "#,##0", "# Ongoing FTE Losses", "#,##0", "# Respite FTE Losses", "#,##0", "# Net FTE Change", "#,##0", "# Billable Hours Monthly", "#,##0", "# Billable Hours Weekly Average", "#,##0", "# Paying Clients", "#,##0", "# Average Hours per Client", "#,##0.0", "# Hours Gained", "#,##0", "# Hours Lost", "#,##0", "# Net change (hours )", "#,##0", "# Starts (Clients)", "#,##0", "# Restarts (Clients)", "#,##0", "# Losses (Clients)", "#,##0", "# Net change (clients )", "#,##0", "# Total Paying Clients", "#,##0", "# Starts", "#,##0", "£ CPSQL", "£#,##0", "£ CPA", "£#,##0", "# Client Losses (exc Pauses)", "#,##0", "% Client churn", "0.0%", "% RIP Client churn", "0.0%", "# Carers Available", "#,##0", "# Carers in placement", "#,##0", "% Carer Pool Utilised", "0.0%", "# Carers Started", "#,##0", "# Carers Lost", "#,##0", "# Net Carer change", "#,##0", "# Hired", "#,##0", "% Carer churn", "0.0%", "#,##0" // default fallback if a KEY RESULT is ever added without a matching format )Please follow below article to understand dyanmic string
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings
Please give kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
Hi masplin
based on what i see , FORMAT function is likely the issue.since format function converts every value to text thats why you are not able to see value options.
try moving your formatting to dynamic string.
Selected Measure Value =
SWITCH(
SELECTEDVALUE('Scorecard'[KEY RESULT]),
"# Enquiries", [Enquiry Count],
"# Qualified Enquiries", [Qualified Enquiry Count],
"# SQLs", [SQL Count],
"# Assessments Completed", [Assessments Taking Place],
"# Registrations", [Registration Fee Paid Count],
"% SQL → Assessment", [RAW SQL-Assessment Converion %],
"% Assessment → Start", [RAW Assess-Start Conversion %],
"% SQL → Client Start (CSA)", [RAW SQL-Start Conversion %],
"% SQL → Registration", [RAW SQL-Registration Converion %],
"% Registration → Start", [RAW Registration-Start Conversion %],
"% SQL → Start", [RAW SQL-Start Conversion %],
"# Monthly FTEs (calculated)", [Live-In FTE Equiv Billable Hours ex Canc],
"# Total FTE Starts", [FTEs Starting in Period],
"# Ongoing FTE Starts", [FTE Starts Ongoing],
"# Respite FTE Starts", [FTE Starts Respite],
"# Ongoing FTE Losses", [FTE Losses Ongoing],
"# Respite FTE Losses", [FTE Losses Respite],
"# Net FTE Change", [Core FTE Change],
"# Billable Hours Monthly", [Billable Hours],
"# Billable Hours Weekly Average", [Billable Hours per Week],
"# Paying Clients", [Distinct Paying Clients],
"# Average Hours per Client", [Avg Billable Hours per Client],
"# Hours Gained", [Client VC Weekly Hours Starting in Period],
"# Hours Lost", [Client VC Weekly Hours Terminating in Period],
"# Net change (hours )", [Client VC Weekly Hours Net],
"# Starts (Clients)", [Clients Starting in Period],
"# Restarts (Clients)", [Restarts Not Paying Previous Months, Paying Current Month and Not New Start],
"# Losses (Clients)", [Clients Terminating in Period],
"# Net change (clients )", [Client Net Gain in Period],
"# Total Paying Clients", [Distinct Paying Clients],
"# Starts", [Clients Starting in Period],
"£ CPSQL", [CPSQL],
"£ CPA", [CPA],
"# Client Losses (exc Pauses)", [Clients Terminating in Period],
"% Client churn", [Client Churn %],
"% RIP Client churn", [Client Churn RIP %],
"# Carers Available", [Carers Active End Period],
"# Carers in placement", [Carers in Placement],
"% Carer Pool Utilised", [Carer Pool Utilisation %],
"# Carers Started", [Carers Starting in Period],
"# Carers Lost", [Carers Terminating in Period],
"# Net Carer change", [Carer Net Change],
"# Hired", [Carers Registering in Period],
"% Carer churn", [Carer Churn]
)
Use below in dynamic string format.
SWITCH(
SELECTEDVALUE('Scorecard'[KEY RESULT]),
"# Enquiries", "#,##0",
"# Qualified Enquiries", "#,##0",
"# SQLs", "#,##0",
"# Assessments Completed", "#,##0",
"# Registrations", "#,##0",
"% SQL → Assessment", "0.0%",
"% Assessment → Start", "0.0%",
"% SQL → Client Start (CSA)", "0.0%",
"% SQL → Registration", "0.0%",
"% Registration → Start", "0.0%",
"% SQL → Start", "0.0%",
"# Monthly FTEs (calculated)", "#,##0.0",
"# Total FTE Starts", "#,##0",
"# Ongoing FTE Starts", "#,##0",
"# Respite FTE Starts", "#,##0",
"# Ongoing FTE Losses", "#,##0",
"# Respite FTE Losses", "#,##0",
"# Net FTE Change", "#,##0",
"# Billable Hours Monthly", "#,##0",
"# Billable Hours Weekly Average", "#,##0",
"# Paying Clients", "#,##0",
"# Average Hours per Client", "#,##0.0",
"# Hours Gained", "#,##0",
"# Hours Lost", "#,##0",
"# Net change (hours )", "#,##0",
"# Starts (Clients)", "#,##0",
"# Restarts (Clients)", "#,##0",
"# Losses (Clients)", "#,##0",
"# Net change (clients )", "#,##0",
"# Total Paying Clients", "#,##0",
"# Starts", "#,##0",
"£ CPSQL", "£#,##0",
"£ CPA", "£#,##0",
"# Client Losses (exc Pauses)", "#,##0",
"% Client churn", "0.0%",
"% RIP Client churn", "0.0%",
"# Carers Available", "#,##0",
"# Carers in placement", "#,##0",
"% Carer Pool Utilised", "0.0%",
"# Carers Started", "#,##0",
"# Carers Lost", "#,##0",
"# Net Carer change", "#,##0",
"# Hired", "#,##0",
"% Carer churn", "0.0%",
"#,##0" // default fallback if a KEY RESULT is ever added without a matching format
)
Please follow below article to understand dyanmic string
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-dynamic-format-strings
Please give kudos or mark it as solution once confirmed.
Thanks and Regards,
Praful
Ah very clever thanks for that