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 

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

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