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 


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.

     

     

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