Forum Discussion

blithechan's avatar
blithechan
Advocate I
8 years ago
Solved

DAX Function CONCATENATEX is not supported on PBIRS / DirectQuery Mode

Hi all,   I tried to use DAX function CONCATENATEX using DirectQuery mode. It works fine on PBI Desktop. But when I run the report on on-prem PBIRS, the following error message "Function CONCANTENA...
CONCATENATEX
DAX
PBIRS
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    Hmm, yeah for something simple like that you could do something like:

     

    Measure = 
MAXX(FILTER('#Currencies',[Currency]="HKD"),[Currency]) & "," &
MAXX(FILTER('#Currencies',[Currency]="USD"),[Currency]) & "," &
MAXX(FILTER('#Currencies',[Currency]="RMB"),[Currency])

    But that's really not any better than just creating a measure that is = "HKD,USD,RMB" and I assume this is a small subset of your data.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

Correct, if it isn't in this list, it isn't supported:

https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016?view=sql-analysis-services-2017

 

Would need to understand exactly what you are trying to do to see if there is a solution. Source data, expected output, etc. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

  • blithechan's avatar
    blithechan
    Advocate I
    8 years ago

    Hi Greg,

     

    Thanks for your quick response.


    What i am trying to do is to concatenate a column's values into a list.

     

    Source data

    Currency
    HKD
    USD
    RMB

     

    Expected Output

    HKD,USD,RMB

     

    Thus CONCATENATEX sounds do exactly what I expect.

     

    Appreciate if you can share any workaround to achieve the same. Thanks so much.

     

    Regards,

    Blithe

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      8 years ago

      Hmm, yeah for something simple like that you could do something like:

       

      Measure = 
MAXX(FILTER('#Currencies',[Currency]="HKD"),[Currency]) & "," &
MAXX(FILTER('#Currencies',[Currency]="USD"),[Currency]) & "," &
MAXX(FILTER('#Currencies',[Currency]="RMB"),[Currency])

      But that's really not any better than just creating a measure that is = "HKD,USD,RMB" and I assume this is a small subset of your data.

      • blithechan's avatar
        blithechan
        Advocate I
        8 years ago

        Thanks Greg for suggesting MAXX function. It serves the purpose well.