Returning comma separated values from a filtered related table

[JFarq]

Hi

I'm trying to create a measure similar to this:

Selected Suppress Labels =
CONCATENATEX(
        VALUES(Main_Inc_CC_And_tfs_SurveyResults[Suppress Label (big 14 2022 - big 14 2024 only)]),
        Main_Inc_CC_And_tfs_SurveyResults[Suppress Label (big 14 2022 - big 14 2024 only)],
        ", ",
	    Main_Inc_CC_And_tfs_SurveyResults[Suppress Label (big 14 2022 - big 14 2024 only)],
	    ASC
        )

This returns a comma separated list of the values in the Suppress Label (big 14 2022 - big 14 2024 only) field based on the current filter/slicer context of my report.

However, to simplify my raw data, i'd like to create a measure to pull the 'Suppress Label' values from a field in a different table connected via a 'CN-Q code' in a one-to-many relationship:

I've tried to adapt a measure i've used to previously to pull data from connecting tables (with a calculate function):

Pull values from ReturnsData_21_22_to_23_24_table =
    CALCULATE (
        CONCATENATEX(
        VALUES(ReturnsData_21_22_to_23_24[Suppress Label]),
        ReturnsData_21_22_to_23_24[Suppress Label],
        ", ",
	    ReturnsData_21_22_to_23_24[Suppress Label],
	    ASC
        ),
        FILTER (
            ReturnsData_21_22_to_23_24,
            ReturnsData_21_22_to_23_24[CN-Q code] == MAXX ( Main_Inc_CC_And_tfs_SurveyResults, Main_Inc_CC_And_tfs_SurveyResults[CN-Q code] )
        )
    )

but it seems to return only one value instead of a comma separated list when more than one are selected in the Main_Inc_CC_And_tfs_SurveyResults[Suppress Label (big 14 2022 - big 14 2024 only)] field (and therefore the ReturnsData_21_22_to_23_24[Suppress Label] field):

Apologies if this doesn't give enough information, i'll upload a .pbix file if necessary.

Any help would be much appreciated

JFarq

[v-yohua-msft · Accepted solution]

Hi, @JFarq 

Maybe you can create measures and try the following DAX expression:

Unique Suppress Labels = 
    CONCATENATEX (
        DISTINCT ( 'Table'[Suppress Label] ),
        'Table'[Suppress Label],
        ", "
    )

Filtered Suppress Labels = 
    CALCULATE (
        [Unique Suppress Labels],
        FILTER (
            'Table',
            'Table'[CN-Q code] = MAXX ( 'Table', 'Table'[CN-Q code] )
        )
    )

Here is my preview:

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Moetazzahran]

Hello @JFarq , 
Can you please upload a PBIX file to investigate the issue?

Thank you.