CONCATENATE with Condition (To be used in Visuals)

[rphang]

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!

[Jos_Woolley]

You're welcome!

Cheers

[Jos_Woolley · Accepted solution]

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

[rphang]

@Jos_Woolley Perfect! This completely works. Thank you!

[Jos_Woolley]

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

[rphang]

@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?