Concatenate unique values of two columns with DAX measure

Anonymous — Fri, 05 Aug 2022 15:09:30 GMT

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

Re: Concatenate unique values of two columns with DAX measure

tamerj1 — Fri, 05 Aug 2022 15:51:18 GMT

Hi Anonymous
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 )

topic Re: Concatenate unique values of two columns with DAX measure in DAX Commands and Tips

Concatenate unique values of two columns with DAX measure

Re: Concatenate unique values of two columns with DAX measure