Forum Discussion

HoppeG76's avatar
HoppeG76
Regular Visitor
2 years ago
Solved

Remove Duplicate Values using CONCATENATEX and Direct Query

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?
 
 
CONCATENATEX
Direct Query
Duplicate Values
SQL
  • HoppeG76's avatar
    HoppeG76
    2 years ago

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

7 Replies

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    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's avatar
      HoppeG76
      Regular Visitor
      2 years ago

      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's avatar
        Dangar332
        Icon for Resident Rockstar rankResident Rockstar
        2 years ago

        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