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...
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],
                ", "
                )
Dangar332's avatar
Dangar332
Resident 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
    Resident 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

    • HoppeG76's avatar
      HoppeG76
      Regular Visitor
      2 years ago

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

        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.