Concatenate unique values of two columns with DAX measure

[JulieB_]

Hi there, 

I need to create a DAX measure that concatenates unique values of two columns in a text measure and sorts the values of the second column. 

See example below:

Column NAME and Text must we concatenated in a text measure.

I also want the Text column to be arranged from high to low values in the measure result.

Sample Table:

WeekNr	NAME	Material Key	Material	amount	quantity	Text
31	Expired Goods	7630674	food	-5656,24	-1032	-5,656.2€ (-1032)  -> food (7630674)
31	Expired Goods	7331045	clothes	-5426,29	-612	-5,426.3€ (-612)  -> clothes (7331045)
31	Expired Goods	7581165	ribs	-2023,73	-900	-2,023.7€ (-900)  -> ribs (7581165)
31	Expired Goods	7329715	shirt	-2129,4	-630	-2,129.4€ (-630)  -> shirt (7329715)
31	Push to	7325844	pants	-7595,5	-3744	-7,595.5€ (-3744)  -> pants (7325844)

Desired DAX measure result:

Expired Goods:
-5,656.2€ (-1032) -> food (7630674),
-5,426.3€ (-612) -> clothes (7331045),

-2,129.4€ (-630) -> shirt (7329715)
-2,023.7€ (-900) -> ribs (7581165),
Push to:
-7,595.5€ (-3744) -> pants (7325844)

I tried this dax measure but it returns the wrong result:

VAR concattext = 
CALCULATE(
    CONCATENATEX(VALUES(WeeklyAnalysis[Text]), WeeklyAnalysis[Text], ", " & UNICHAR ( 10 )),
    RELATEDTABLE(WeeklyAnalysis)
)
VAR names = 
CALCULATE(
    CONCATENATEX(VALUES(WeeklyAnalysis[NAME]), WeeklyAnalysis[NAME] & concattext , ", " & UNICHAR ( 10 )),
    RELATEDTABLE(WeeklyAnalysis)
)
RETURN
names

It returns the wrong result: 

Scrap Expired Goods:
-5,656.2€ (-1032) -> food (7630674),
-5,426.3€ (-612) -> clothes (7331045),
-2,023.7€ (-900) -> ribs (7581165),
-2,129.4€ (-630) -> shirt (7329715),
-7,595.5€ (-3744) -> pants (7325844)
Push to:
-5,656.2€ (-1032) -> food (7630674),
-5,426.3€ (-612) -> clothes (7331045),
-2,023.7€ (-900) -> ribs (7581165),
-2,129.4€ (-630) -> shirt (7329715),
-7,595.5€ (-3744) -> pants (7325844)

Someone an idea? 

Kind regards,

Julie

[tamerj1 · Accepted solution]

Hi @JulieB_ 
Here is a sample file with the solutionhttps://www.dropbox.com/t/fhLtT0k3tEPToqGP

Result = 
CONCATENATEX (
    VALUES ( WeeklyAnalysis[NAME] ),
    WeeklyAnalysis[NAME] & UNICHAR ( 10 ) &
    CALCULATE (
        CONCATENATEX ( 
        WeeklyAnalysis,
        WeeklyAnalysis[Text], UNICHAR ( 10 ), WeeklyAnalysis[Text], DESC 
        )
    ), UNICHAR ( 10 ), WeeklyAnalysis[NAME], ASC 
)