topic Re: CONCATENATE with Condition (To be used in Visuals) in DAX Commands and Tips

CONCATENATE with Condition (To be used in Visuals)

rphang — Fri, 22 Oct 2021 04:44:13 GMT

Hi there,

Apologies first hand if there's a similar request within the forum.

I need some help & advice getting around presenting my data on a donut visual. Not sure by using Measures or adding custom column would be the most ideal solution. Below is the sample Table and the results I want to achieve.

Within each of my transactions, there are a few item type and sub-type; "Type A" and "Type B".

Table Name: Table X

Transaction No.	Type A	Type B
A0001	Fragrance	Sales Product
A0001	Fragrance	Sales Product
A0001	Fragrance	Samples
A0001	Make-up	Samples
A0001	Make-up	Sales Product
A0001	Fragrance	Sales Product
A0002	Make-up	Samples
A0002	Make-up	Sales Product
A0002	Skincare	Sales Product
A0003	Skincare	Sales Product
A0003	Fragrance	Sales Product
A0003	Make-up	Samples
A0003	Skincare	Sales Product
A0004	Make-up	Sales Product
A0004	Make-up	Samples
A0004	Make-up	Sales Product

I would like to have all the "Type A" to be converted into Acronyms (FR / MU / SC) before concatenation, using the delimiter of "&". (Table & Donut visual below)

The concatenation does not need to be in a fixed sequence, however, if there's a result of "FR & MU" and " MU & FR", they should be combined as one Category when showing as a donut.

However, concatenation should omit criteria "Type B"; Type A that does not belong to "Sales Product".

Transaction No.	Type A (Category)
A0001	FR & MU
A0002	MU & SC
A0003	SC & FR
A0004	MU

Currently I am using the following DAX measures which takes into account all Type A regardless if it is a Sales Product or Samples.

CALCULATE(CONCATENATEX(VALUES('Table X'[Type A]),'Table X'[Type A]," & "))

Thank you in advance!

Re: CONCATENATE with Condition (To be used in Visuals)

Jos_Woolley — Fri, 22 Oct 2021 07:59:56 GMT

I'd first create another two-column table to store the Type A abbreviations, named Type A Abbreviations. After which the required measure is:

MyMeasure = CONCATENATEX ( DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Table X', 'Table X'[Type B] = "Sales Product" ), "Type A", 'Table X'[Type A] ) ), LOOKUPVALUE ( 'Type A Abbreviations'[Abbreviation], 'Type A Abbreviations'[Type A], [Type A] ), " & " )

Regards

Re: CONCATENATE with Condition (To be used in Visuals)

rphang — Fri, 22 Oct 2021 09:21:03 GMT

Jos_Woolley

Thank you, I created a seperated table and relationship between Type A.

I used your code as "New Column" instead of Dax measures since I want to use it under Legend within the donut visual. But I am getting "FR & MU & SC" for all transaction.

Can I check what modification needs to be done to the coding? Or I place the code on the wrong table?

Re: CONCATENATE with Condition (To be used in Visuals)

Jos_Woolley — Fri, 22 Oct 2021 09:39:06 GMT

Ah, for a calculated column use:

NewColumn = CONCATENATEX ( DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Table X', 'Table X'[Type B] = "Sales Product" && 'Table X'[Transaction No.]=EARLIER('Table X'[Transaction No.] )), "Type A", 'Table X'[Type A] ) ), LOOKUPVALUE ( 'Type A Abbreviations'[Abbreviation], 'Type A Abbreviations'[Type A], [Type A] ), " & " )

Regards

Re: CONCATENATE with Condition (To be used in Visuals)

rphang — Fri, 22 Oct 2021 10:18:07 GMT

Jos_Woolley Perfect! This completely works. Thank you!

Re: CONCATENATE with Condition (To be used in Visuals)

Jos_Woolley — Fri, 22 Oct 2021 11:27:03 GMT

You're welcome!

Cheers