Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Concatenate a field within IF/SWITCH statement

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

Need Help
  • parry2k's avatar
    parry2k
    5 years ago

    Anonymous well adding the column at the source is your best bet.

7 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Anonymous well adding the column at the source is your best bet.

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    the calculated column you posted should work.

    What exactly happens when you use the formula you pasted?

     

    Best regards

    Denis

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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..

       

       

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 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.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

     

  • Venkat4400's avatar
    Venkat4400
    Frequent Visitor
    5 years ago

    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