Forum Discussion

s4muel's avatar
s4muel
Frequent Visitor
1 year ago
Solved

Transform a text dimension into a measure?

Hello,

My business case: create a measure (green frame) that displays a dimension (blue frame) by replacing the value separator ( | ) by a line break.

 

 

 

 

 

 

 

 

 

 

 

Here my DAX (and that works)

 

IF(ISINSCOPE('Attribute Names Schema'[Attribute name]),
    CONCATENATEX(
        CALCULATETABLE(
            ADDCOLUMNS(
                VALUES('Attribute Names Schema'[Value range]),
                "measure", SUBSTITUTE('Attribute Names Schema'[Value range], "|", UNICHAR(10))
            )
        ),
        [measure]
    )
)
 
Isn't there a better/easier/more conventional instruction to do the same thing?
 
Thanks in advance for the support
Need Help
Tips and Tricks
  • freginier's avatar
    freginier
    1 year ago

    Hey there!

     

    If I understand correctly, you are trying to replace the " | " separator in a DAX measure with a line break (UNICHAR(10)). 

     

    You can simplify your DAX measure by directly using CONCATENATEX on the VALUES function without the need for CALCULATETABLE or ADDCOLUMNS, like this:

    Measure =
    IF(
    ISINSCOPE('Attribute Names Schema'[Attribute name]),
    CONCATENATEX(
    VALUES('Attribute Names Schema'[Value range]),
    SUBSTITUTE([Value range], "|", UNICHAR(10)),
    UNICHAR(10)
    )
    )

     

    This should give you a correct result!

    Hope this helps! 

    😁😁

  • danextian's avatar
    danextian
    1 year ago

    Hi s4muel 

     

    CONCATENATEX isn't necessary but here are two measures that uses it and another one that doesnt

    FormattedText = 
IF (
    ISINSCOPE ( 'Table'[Attribute] ),
    CONCATENATEX (
        'Table',
        SUBSTITUTE ( 'Table'[Value range], "|", UNICHAR ( 10 ) ),
        UNICHAR ( 10 )
    )
)

    FormattedText2 = 
IF (
    ISINSCOPE ( 'Table'[Attribute] ),
    SUBSTITUTE ( SELECTEDVALUE ( 'Table'[Value range] ), "|", UNICHAR ( 10 ) )
)

    Please see the attached pbix.

     

5 Replies

  • freginier's avatar
    freginier
    Icon for Solution Sage rankSolution Sage
    1 year ago

    Hey there!

     

    If I understand correctly, you are trying to replace the " | " separator in a DAX measure with a line break (UNICHAR(10)). 

     

    You can simplify your DAX measure by directly using CONCATENATEX on the VALUES function without the need for CALCULATETABLE or ADDCOLUMNS, like this:

    Measure =
    IF(
    ISINSCOPE('Attribute Names Schema'[Attribute name]),
    CONCATENATEX(
    VALUES('Attribute Names Schema'[Value range]),
    SUBSTITUTE([Value range], "|", UNICHAR(10)),
    UNICHAR(10)
    )
    )

     

    This should give you a correct result!

    Hope this helps! 

    😁😁

    • s4muel's avatar
      s4muel
      Frequent Visitor
      1 year ago

      Hello freginier

      Yes it give me the same result (and by the way the last UNICHAR(10) seems even not to be necessary 😀 )

      To be honest, I would have thought to be challenged on the CONCATENATEX and not the CALCULATETABLE(ADDCOLUMNS.

      So, to transform a text dimension into a measure CONCATENATEX is always the correct instruction?

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi s4muel 

     

    CONCATENATEX isn't necessary but here are two measures that uses it and another one that doesnt

    FormattedText = 
IF (
    ISINSCOPE ( 'Table'[Attribute] ),
    CONCATENATEX (
        'Table',
        SUBSTITUTE ( 'Table'[Value range], "|", UNICHAR ( 10 ) ),
        UNICHAR ( 10 )
    )
)

    FormattedText2 = 
IF (
    ISINSCOPE ( 'Table'[Attribute] ),
    SUBSTITUTE ( SELECTEDVALUE ( 'Table'[Value range] ), "|", UNICHAR ( 10 ) )
)

    Please see the attached pbix.

     

    • s4muel's avatar
      s4muel
      Frequent Visitor
      1 year ago

      Hello danextian ,

      Great thank you!

      Just 1 question, in the FormattedText formula, what is the 2nd "UNICHAR(10)" for please?

      • danextian's avatar
        danextian
        Icon for Super User rankSuper User
        1 year ago

        Good catch. The second one is not necessary. You could use "" and will still get the same result visually.