Concatenate a field within IF/SWITCH statement

[RushiS]

Hello Everyone, 

I'm new to Power BI. I have created new column in my report using this SWITCH statement to group Instituitions based on below conditions.  What i want is Concat "Control Group1" with INSTITUTIONS[NAME] withing the SWITCH statement.  Tried Using CONCATENATE  and COMBINEVALUE functions as well as below method. All of those gives me " Error Message:
OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.." Error.. Im using Direct Query mode in My Report.

Appreciate If anyone can suggest me a method to concat a string with Table[ColumnName] within a SWITCH Statement

INSTITUTIONS GROUP= SWITCH ( TRUE (),
INSTITUTIONS[ID] = "d3a2efa8" || INSTITUTIONS[ID] = "721be8893","Control Group1" & " - " & INSTITUTIONS[NAME],
INSTITUTIONS[ID]="729dbb5a-"
|| INSTITUTIONS[ID] ="b7ea8f29"
|| INSTITUTIONS[ID] ="6d85fa58"
|| INSTITUTIONS[ID] ="1df7d3ed","Control Group2","Control Group3")

Thanks

[Venkat4400]

Hi,

I can propose two options: Just try and let me know

1> Make sure that column data type is correct (Text,Whole number etc) - Check at front end and back end

If yes,try to create the formula by using Cal.Column 

2>If it doesn't help - Push this logic to back end,so that you can get rid of this issue and also improve the query performance (since you were using direct mode)

Regards,

Venkat

[parry2k · Accepted solution]

@RushiS well adding the column at the source is your best bet.

[RushiS]

PS :  The reason why im using Direct Query is that tables im using are huge.

when im creating column using DAX expression I pasted above it does not throw any error. Once I add it to table visualization it throws below error

*** Any help will be highly appreciated

---------------

Feedback Type:
Frown (Error)

Timestamp:
2021-06-08T15:56:25.2316632Z

Local Time:
2021-06-08T21:26:25.2316632+05:30

Session ID:
655fe6df-4135-4b0b-bb07-03dbb5e2aaad

Release:
April 2021

Product Version:
2.92.1067.0 (21.04) (x64)

Error Message:
OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression..

OS Version:
Microsoft Windows NT 10.0.19042.0 (x64 en-US)

CLR Version:
4.7 or later [Release Number = 528372]

Peak Virtual Memory:
38.5 GB

[parry2k]

@RushiS I hardly uses DQ but two things:

- why not add this column at the backend source so as not to create it in Power BI

or try the following expression:

NSTITUTIONS GROUP= SWITCH ( TRUE (),

INSTITUTIONS[ID] IN { "d3a2efa8" , "721be8893" },"Control Group1" & " - " & INSTITUTIONS[NAME],
INSTITUTIONS[ID] IN { "729dbb5a-", "b7ea8f29", "6d85fa58", "1df7d3ed" },"Control Group2",
"Control Group3")

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[RushiS]

@parry2k Thanks for the suggestion . I tried that way but it throws same error.. i posted below

[selimovd]

Hey @RushiS ,

the calculated column you posted should work.

What exactly happens when you use the formula you pasted?

Best regards

Denis

[RushiS]

My table visualization displays an error. im attaching Error and Screenshot here.

Error Message:
OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression..