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

 

 

  • HoppeG76's avatar
    HoppeG76
    Regular Visitor
    2 years ago

    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. 

  • HoppeG76's avatar
    HoppeG76
    Regular Visitor
    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],
                ", "
                )