Custom Column to convert an ID text string to a text Value (SWITCH function?)

[ericdpalmer]

To overcome a database bug I need to filter my data by a 36 character 'ID' field but the executive audience for my report wants to see the corresponding 'Value' (aka. Name) of each on the Report Slicer.  After some light googling/searching on this forum I attempted to create a new custom column that would utilize the SWITCH function.

Example of my goal:

Existing Column "PlanType_EnterpriseProject_ID" returns = aad0e900-ae1c-435b-b3b7-0be5242539fb

New Custom Column "PlanType_EnterpriseProject_IDtoValue" returns = Improve Processess, Systems, and Infrastructure

Here is my attempt at creating the custom column:

=SWITCH( [PlanType_EnterpriseProject_Id], "aad0e900-ae1c-435b-b3b7-0be5242539fb", "Improve Processess, Systems, and Infrastructure", "b85b8606-1972-4f63-ace2-1a9e0f8bf195", "Optimize Core Products", "81b6abcb-8488-47e7-8d26-5f36de8fd2a3", "Build a High Performing Team", "fd8cabb0-f2ad-4ec6-b59f-76aeedb0dde2", "Coach People to Prosperity", "aa290c8f-a1e9-4dc3-8017-259d09bb411e", "Enterprise Facilities Projects", "b234318e-11aa-469b-b080-d0c4d93079da", "Test Portfolio")

This was the error message:

I am still very early in my Power BI journey so I'm sure there is something simple I'm missing or didn't search for the appropriate criteria.  Any help is appreciated!  

[PhilipTreacy · Accepted solution]

Hi @ericdpalmer 

Download example PBIX file

SWITCH is a DAX function and can't be used in Power Query, which is where you are trying to write the formula shown in your screenshot.

In Power Query you will need to use nested if functions.  Or you can do this in DAX too using SWITCH.

In Power Query you create the column like this 

if [PlanType_EnterpriseProject_ID] = "aad0e900-ae1c-435b-b3b7-0be5242539fb" then "Improve Processess, Systems, and Infrastructure"

else if [PlanType_EnterpriseProject_ID] = "b85b8606-1972-4f63-ace2-1a9e0f8bf195" then "Optimize Core Products" 

else if [PlanType_EnterpriseProject_ID] = "81b6abcb-8488-47e7-8d26-5f36de8fd2a3" then "Build a High Performing Team"

else if [PlanType_EnterpriseProject_ID] = "fd8cabb0-f2ad-4ec6-b59f-76aeedb0dde2" then "Coach People to Prosperity" 

else if [PlanType_EnterpriseProject_ID] = "aa290c8f-a1e9-4dc3-8017-259d09bb411e" then "Enterprise Facilities Projects" 

else if [PlanType_EnterpriseProject_ID] = "b234318e-11aa-469b-b080-d0c4d93079da" then "Test Portfolio" 

else null 

To create the column in DAX (after loading the data from Power Query into Power BI) you can write this

DAX Column = SWITCH(

    [PlanType_EnterpriseProject_Id],

    "aad0e900-ae1c-435b-b3b7-0be5242539fb", "Improve Processess, Systems, and Infrastructure", 
    
    "b85b8606-1972-4f63-ace2-1a9e0f8bf195", "Optimize Core Products", 
    
    "81b6abcb-8488-47e7-8d26-5f36de8fd2a3", "Build a High Performing Team", 
    
    "fd8cabb0-f2ad-4ec6-b59f-76aeedb0dde2", "Coach People to Prosperity", 
    
    "aa290c8f-a1e9-4dc3-8017-259d09bb411e", "Enterprise Facilities Projects", 
    
    "b234318e-11aa-469b-b080-d0c4d93079da", "Test Portfolio",

    "Not Found"

)

Note that SWITCH requires a default result which in my function above is the last line that returns "Not Found".  This default is in case none of the preceding conditions are met (found to be true).

The Power Query if is similar in that it requires a final else statement, which I have used to output null if none of the ID's are found.  

See attached file for examples of both.

Regards

Phil

[ericdpalmer]

Thank you so much!  Not only did this solve my issue but I have a much better understanding.