Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAx Measure error Concatenate

Hi Experts

 

I cannot see where my error is on the following DAX - the end result should bu (Canada),(Italy);[004],[005]

 

 

Measure 2 = 

VAR _CountryStore = "(Cananda),(Italy);[004],[005]"

VAR SeparatorPosition = SEARCH(";",_CountryStore)

VAR Country = LEFT(_CountryStore, SeparatorPosition - 1)
VAR Store = RIGHT(_CountryStore , SeparatorPosition + 1)

RETURN
CONCATENATE(Country, CONCATENATE("(",CONCATENATE(Store,")"))

)

 

 

 

Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    RIGHT starts counting from the end of the text, not the beginning. Try

    Measure2 =
VAR _CountryStore = "(Cananda),(Italy);[004],[005]"
VAR SeparatorPosition =
    SEARCH ( ";", _CountryStore )
VAR _Length =
    LEN ( _CountryStore )
VAR _Country =
    LEFT ( _CountryStore, SeparatorPosition - 1 )
VAR _Store =
    MID ( _CountryStore, SeparatorPosition + 1, _Length - SeparatorPosition )
RETURN
    CONCATENATE ( _Country, CONCATENATE ( "(", CONCATENATE ( _Store, ")" ) ) )

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    RIGHT starts counting from the end of the text, not the beginning. Try

    Measure2 =
VAR _CountryStore = "(Cananda),(Italy);[004],[005]"
VAR SeparatorPosition =
    SEARCH ( ";", _CountryStore )
VAR _Length =
    LEN ( _CountryStore )
VAR _Country =
    LEFT ( _CountryStore, SeparatorPosition - 1 )
VAR _Store =
    MID ( _CountryStore, SeparatorPosition + 1, _Length - SeparatorPosition )
RETURN
    CONCATENATE ( _Country, CONCATENATE ( "(", CONCATENATE ( _Store, ")" ) ) )