How to concatenate values filtered by measure?

[Anonymous]

I'm working on a file where I have a hierarchical slicer and matrix visualization. In order to filter the matrix columns, I built a measure to check if all slicer criteria is met, which returns 1 or 0.

match_check = 
VAR NumOfSelectedAttrVal = DISTINCTCOUNT ( 'my_table'[ATTRIB_VALUE] ) 
VAR SelectedNumCols = 
    CALCULATE(
        DISTINCTCOUNT ( 'my_table'[ATTRIB_NAME] ) ,
        ALLSELECTED(my_table)
    )
VAR FiltersNotApplied = AND(ISFILTERED(my_table[ATTRIB_NAME])=false, ISFILTERED(my_table[ATTRIB_VALUE])=false)
VAR bool = IF(OR(NumOfSelectedAttrVal = SelectedNumCols, FiltersNotApplied = true), 1, 0)
RETURN
    bool

I apply the measure as a visual level filter to only show records where the match_check = 1

Then I'm able to count the matching records by summing the measure column.

Records = 
VAR match_record_count = 
    SUMX(SUMMARIZE(my_table,my_table[record_id],"measure",[match_check]),[measure])
var record_count = 
IF(match_record_count=0,
    IF(ISFILTERED(my_table[ATTRIB_VALUE]),
        match_record_count,
        DISTINCTCOUNT(my_table[record_id])),
    match_record_count)
return record_count

Now I want to concatenate the matching records to create a comma separated list, but I'm having trouble doing so. I thought that concatenating the filtered table would work, but I get 0 results when trying something like this:

record_list = 
    CONCATENATEX(
        FILTER(my_table,
        [match_check]=1),
        my_table[record_id],
        ",")

Is it possible in this case to concatenate the record_id values where the match_check = 1?

[Anonymous · Accepted solution]

Posting my own solution for others to see. I ended up creating a SUMMARIZE table with the match_check included and filtering on that:

record_list = 
   CONCATENATEX(
      FILTER(
         SUMMARIZE(my_table,
         my_table[record_id],
         "bool",
         [match_check]),
      [bool]=1),
   my_table[record_id],",")

[MyroslavaM]

Thank you for sharing! Helped me as well!

[v-jingzhang]

Hi @Anonymous 

I believe it is possible to concatenate the record_id values but it would be better if you could provide some sample data or a sample file for us to test the measure.

And which visual is this measure put in? Is there other field in the same visual? If it returns 0 result, the FITLER part seems to return blank result which means no rows meet the slicer criteria in the current context. So you need to check how the slicer and other filters influence the visual currently. 

Maybe you can try adding ALL or ALLSELECTED to the current measure. 

record_list = 
    CONCATENATEX(
        FILTER(ALLSELECTED(my_table),
        [match_check]=1),
        my_table[record_id],
        ",")

Best Regards,
Community Support Team _ Jing

[Anonymous]

I ended up getting to my intended outcome by creating a table using SUMMARIZE and filtering that. See my own reply with the accepted solution.