Remove Duplicate Values using CONCATENATEX and Direct Query

[HoppeG76]

I am having trouble getting values from a Direct Query to appear correctly in a table using the CONCATENATEX fxn. I have tried multiple fxn setups and all present varying issues. Each row corresponds to a specific ID and each Finding (bmpObs) only appears once in the SQL dataset for each ID.

The SQL dataset looks similar to this:

ID#1 - value1

ID#1 - NULL

ID#1 - value2

ID#1 - value3

ID#1 - NULL

ID#2 - value1

ID#2 - NULL

ID#2 - value4

ID#2 - value6

ID#2 - NULL

ID#3 - value2

ID#3 - NULL

ID#3 - value4

ID#3 - value5

ID#3 - NULL

I'm trying to get a result that looks like:

ID#1 - value1, value2, value3

ID#2 - value1, value4, value6

ID#3 - value2, value4, value5

Here are the DAX formulas I've tried using, in addition to others I can't remember at the moment.

1.

List of BMP observations =

VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT(FindingsFacilityVisualList[bmpObs])

RETURN

            CONCATENATEX(

                DISTINCT(

                    FILTER(FindingsFacilityVisualList,

                    FindingsFacilityVisualList[bmpObs] <> BLANK()

                    )

                ),

            FindingsFacilityVisualList[bmpObs],

            ", "

            ) 

Results in duplicated values:

ID#1 - value1, value2, value3, value1, value2, value3, value1, value2, value3

ID#2 - value1, value4, value6, value1, value4, value6, value1, value4, value6

ID#3 - value2, value4, value5, value2, value4, value5, value2, value4, value5

---------

2.

List of BMP observations =

VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT('FindingsFacilityVisualList'[bmpObs])

RETURN

            CALCULATE(CONCATENATEX(

                DISTINCT(

                    FindingsFacilityVisualList[bmpObs]),

                    FindingsFacilityVisualList[bmpObs],

            ", "

            ))

Results in a comma before the first value:

ID #1 - , value1, value2, value3

ID #2 - , value1, value4, value6

ID #3 - , value2, value4, value5

---------

3.

List of BMP observations =

CONCATENATEX(

    CALCULATETABLE(

        VALUES(FindingsFacilityVisualList[bmpObs]),

        ALLEXCEPT(FindingsFacilityVisualList, FindingsFacilityVisualList[bmpObs]),

        NOT ISBLANK(FindingsFacilityVisualList[bmpObs])

    ),

    FindingsFacilityVisualList[bmpObs],

    ",  "

)

Results in incorrect values:

ID#1 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6

ID#2 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6

ID#3 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6

ID#4 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6

ID#5 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6

*ID#4 & #5 should not have values associated with them

---------

4.

List of BMP observations =

 CONCATENATEX (

        FILTER ( FindingsFacilityVisualList, LEN ( FindingsFacilityVisualList[bmpObs] ) > 0 ),

        FindingsFacilityVisualList[bmpObs],

        ", "

    )

Results in Error:

Can't display the visual.

---------

Any suggestions for alternate ways to get to my desired end result?

[Dangar332]

Hi, @HoppeG76 

try below measure
just adjust your table and column name
i am taking some different but end goal is same as you want

Measure 4 = 
CONCATENATEX(
     FILTER(
         ALL('Table (5)'[Column1]),
          'Table (5)'[Column1]<>BLANK()
        ), 
    'Table (5)'[Column1], ", "
)

[HoppeG76]

@Dangar332 Thank you for your response, unfortunately this provides the same result as the third method in my post. 

Results in incorrect values:

ID#1 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6

ID#2 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6

ID#3 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6

ID#4 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6

ID#5 - value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6, value1, value2, value3, value4, value5, value6

*ID#4 & #5 should not have values associated with them

** I edited my original post for additional clarity about how my data values differ

[Dangar332]

Hi, @HoppeG76 

pleae check below pbix. file with your output
click HERE 

if it not help then provide sample file with removing of sensitive data

[HoppeG76]

@Dangar332 Because this a Direct Query, I don't know how I would provide a sample to you, unfortunately. Thank you for your assistance.

[Dangar332]

hi, @HoppeG76 

this function not support with direct query

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

try to use in measure form

check official site of microsoft HERE 

and check this HERE 

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

[HoppeG76 · Accepted solution]

@Dangar332 I figured out the issue. It had something to do with the ordering of the relations to tables in the Model View - I shortened the path and it started working correctly using the following DAX:

List of BMP observations =

VAR __DISTINCT_VALUES_COUNT = DISTINCTCOUNT(FindingsFacilityVisualList[bmpObs])

RETURN

            CONCATENATEX(

                DISTINCT(

                    FILTER(FindingsFacilityVisualList,

                    FindingsFacilityVisualList[bmpObs] <> BLANK()

                    )

                ),

            FindingsFacilityVisualList[bmpObs],

            ", "

            )

[HoppeG76]

I have used it in other applications of Direct Query in the same dashboard, no issue, so I'm not sure why it's giving me an issue now.