Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ce138867
Resolver I
Resolver I

Using CONCATENATEX with FILTERS

I've been reviewing previous posts about using CONCATENATEX with FILTERS and I'm seeing different answers to either create a Measure or a new column. I want to create a new Measure so that it can be displayed as a Card based on a page filter. 

 

  • The page filter can be set for an organization
  • Based on filters in the same table, concatenate the values into one string
  • The Measure is one of multiple that is being created to show different pieces of data separately

I was getting an error previously that stated: "A table of multiple values was supplied where a single value was expected." I've been playing with this measure for awhile and wondering if anyone has any recommendations.

With the below query, I'm now getting "Too many arguments were passed to the FILTERS function. The maximum argument count for the function is 1."

Basically if the below filters are applied, I want to display the values from the Vendor Column in this Hosp_Tech table as one string. E.g. if it's Tmobile and Sprint, I want the measure to output both so I can pull into a Card visual.

 
WC =
CONCATENATEX (
    summarize(
        filters(
            Hosp_Tech, Hospital_Tech[CATEGORY] = "IS Infrastructure - Software"
            && Hospital_Tech[TECHNOLOGY] = "Wireless Carrier")
)
)

Thanks in advance.

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @ce138867 

 

You can try this measure

Vendors = 
CONCATENATEX (
    FILTER (
        'Hospital_Tech',
        'Hospital_Tech'[Category] = "IS Infrastructure - Software"
            && 'Hospital_Tech'[Technology] = "Wireless Carrier"
    ),
    'Hospital_Tech'[Vendor],
    "; "
)

vjingzhang_0-1683697170270.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

Hi @ce138867 

 

You can try this measure

Vendors = 
CONCATENATEX (
    FILTER (
        'Hospital_Tech',
        'Hospital_Tech'[Category] = "IS Infrastructure - Software"
            && 'Hospital_Tech'[Technology] = "Wireless Carrier"
    ),
    'Hospital_Tech'[Vendor],
    "; "
)

vjingzhang_0-1683697170270.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Thanks @v-jingzhang , your solution allowed me to remove the visual filter. One additional question if I may. I noticed that this gives me a concatenated list, but I'm seeing duplicates. When I try the below using DISTINCT, I'm still getting the same exact list as without using DISTINCT. Am I using DISTINCT incorrectly here? There were no errors that showed up.

 

Vendors = CONCATENATEX(

DISTINCT(

FILTER ( 'Hospital_Tech', 'Hospital_Tech'[Category] = "IS Infrastructure - Software" && 'Hospital_Tech'[Technology] = "Wireless Carrier" )), 'Hospital_Tech'[Vendor], "; " )

ce138867
Resolver I
Resolver I

After reviewing this I think I was able to solve this but would like some recommendations if this is the best way to do this.  

First, I reviewed this post: https://community.powerbi.com/t5/Desktop/Concatenate-rows-with-filter/m-p/155779#M67448 and created a measure that used the concatenatex function for the Vendor Column. Then I created a card with that measure and applied a filter just for that visual that is Technology = Wireless Carrier. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.