I want to create dax function that uses concatenatex in measure or calculated table

[Sudip_J]

Hi Teams,
I want to write concatenates dax function to group subcategory according to their category group .
Here is example -

and i want this following output -

 
I want this for measures and calculatedtable as well
Thanks & Regards
Sudeep Kumar

[rajendraongole1 · Accepted solution]

Hi @Sudip_J - create a calculated table with one row per Category and a comma-separated list of Subcategories.

CategoryWithSubcategories =
ADDCOLUMNS (
VALUES ( 'YourTable'[Category] ),
"Subcategories",
CONCATENATEX (
FILTER (
'YourTable',
'YourTable'[Category] = EARLIER ( 'YourTable'[Category] )
),
'YourTable'[Subcategory],
", ",
'YourTable'[Subcategory],
ASC
)
)

using measure:

Subcategories Measure =
CONCATENATEX (
VALUES ( 'YourTable'[Subcategory] ),
'YourTable'[Subcategory],
", ",
'YourTable'[Subcategory],
ASC
)

Hope this works and helps.

[Alex_Sawdo · Accepted solution]

You can do this with a DAX measure, here's the code: 

Measure =
CONCATENATEX(
    DISTINCT(
        SELECTCOLUMNS(
            {{YOUR_TABLE}},
            {{YOUR_TABLE}}[Category],
            {{YOUR_TABLE}}[Sub_Category]
        )
    ),
    {{YOUR_TABLE}}[Sub_Category],
    ", "
)

Whenever this measure is present with a Category, it will show all Sub_Categories underneath it in a comma-delimeted list. No need for a seperate calculated table at all.

[Ashish_Excel · Accepted solution]

Hi,

Write this measure

Measure = CONCATENATEX(VALUES(Data[Sub_Category]),Data[Sub_Category],",")

[Sudip_J]

Hi Rajendra,
Thanks for providing the answer .Let me inform you that your dax measure works but for calculated table it is still not working .

[v-nmadadi-msft]

Hi @Sudip_J 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-nmadadi-msft · Accepted solution]

Hi @Sudip_J  ,
Thanks for reaching out to the Microsoft fabric community forum.

 I Have you used this Dax measure to create the output you required.

SubCategoryList_Measure = 
CONCATENATEX(
    VALUES('Table'[Sub_Category]),
    'Table'[Sub_Category],
    ", "
)

 This is the screenshot of the output that I got

 Uploading the .pbix file for reference, Please check and let us know if you would like any changes to the output.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank you

[Ashish_Excel · Accepted solution]

Hi,

Write this measure

Measure = CONCATENATEX(VALUES(Data[Sub_Category]),Data[Sub_Category],",")

[Sudip_J]

Hi Rajendra ,
Thanks for taking the time to respond! I appreciate the input.The solution you provided seems to mismatch
Could you please clarify if I misunderstood something? I attached your answer in the below attached image please go through it .

[Alex_Sawdo · Accepted solution]

You can do this with a DAX measure, here's the code: 

Measure =
CONCATENATEX(
    DISTINCT(
        SELECTCOLUMNS(
            {{YOUR_TABLE}},
            {{YOUR_TABLE}}[Category],
            {{YOUR_TABLE}}[Sub_Category]
        )
    ),
    {{YOUR_TABLE}}[Sub_Category],
    ", "
)

Whenever this measure is present with a Category, it will show all Sub_Categories underneath it in a comma-delimeted list. No need for a seperate calculated table at all.

[rajendraongole1 · Accepted solution]

Hi @Sudip_J - create a calculated table with one row per Category and a comma-separated list of Subcategories.

CategoryWithSubcategories =
ADDCOLUMNS (
VALUES ( 'YourTable'[Category] ),
"Subcategories",
CONCATENATEX (
FILTER (
'YourTable',
'YourTable'[Category] = EARLIER ( 'YourTable'[Category] )
),
'YourTable'[Subcategory],
", ",
'YourTable'[Subcategory],
ASC
)
)

using measure:

Subcategories Measure =
CONCATENATEX (
VALUES ( 'YourTable'[Subcategory] ),
'YourTable'[Subcategory],
", ",
'YourTable'[Subcategory],
ASC
)

Hope this works and helps.