topic Re: Remove Duplicate Values using CONCATENATEX and Direct Query in DAX Commands and Tips

Remove Duplicate Values using CONCATENATEX and Direct Query

HoppeG76 — Thu, 09 Nov 2023 18:58:18 GMT

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.

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

---------

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

---------

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

---------

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?

Re: Remove Duplicate Values using CONCATENATEX and Direct Query

Dangar332 — Thu, 09 Nov 2023 18:00:45 GMT

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], ", " )

Re: Remove Duplicate Values using CONCATENATEX and Direct Query

HoppeG76 — Thu, 09 Nov 2023 19:00:18 GMT

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

Re: Remove Duplicate Values using CONCATENATEX and Direct Query

Dangar332 — Thu, 09 Nov 2023 19:05:15 GMT

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

Re: Remove Duplicate Values using CONCATENATEX and Direct Query

HoppeG76 — Thu, 09 Nov 2023 19:10:40 GMT

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

Re: Remove Duplicate Values using CONCATENATEX and Direct Query

Dangar332 — Thu, 09 Nov 2023 19:19:51 GMT

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.

Re: Remove Duplicate Values using CONCATENATEX and Direct Query

HoppeG76 — Thu, 09 Nov 2023 19:26:35 GMT

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.

Re: Remove Duplicate Values using CONCATENATEX and Direct Query

HoppeG76 — Fri, 10 Nov 2023 16:02:55 GMT

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],

", "

)