Forum Discussion

blithechan's avatar
blithechan
Icon for Advocate I rankAdvocate 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.

blithechan's avatar
blithechan
Icon for Advocate I rankAdvocate 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
Icon for Community Champion rankCommunity 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
    Icon for Advocate I rankAdvocate I
    8 years ago

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