SUMMARIZE and CONCATENATEX with filtering (possibly need CONTAINS) - help?!

[D_PBI]

Hi,

I have the below DAX which works as expected:

Concatenate_Tags =
          SUMMARIZE(
                               patent_PATENTS,
                               patent_PATENTS[Reference Number],
                               "Tags", CONCATENATEX( VALUES( patent_PATENTS[Tag Name] ), patent_PATENTS[Tag Name], ", " )
                               )

What I would like to do is to ONLY include in the concatenation Tag Name containing "BRC" and Tag Name not equal to "no BRC".

How do I achieve this? 

I thinking I need to REMOVE rows from the patent_PATENTS table where the column Tag Name does not contain "BRC" and equals "no BRC". Below is my attempt in doing this:

Concatenate_Tags =

          SUMMARIZE(

                               FILTER(

                                          patent_PATENTS,

                                         AND(

                                                  CONTAINS( patent_PATENTS , patent_PATENTS[Tag Name], "BRC" ),

                                                  patent_PATENTS[Tag Name] <> "no BRC"

                                                 )

                                         ),

                              patent_PATENTS[Reference Number],

                              "Tags", CONCATENATEX( VALUES( patent_PATENTS[Tag Name] ), patent_PATENTS[Tag Name], ", " )

)

However, this is not working. Please can advise help?
Thanks.

[Anonymous · Accepted solution]

This may work:

ADDCOLUMNS(
	VALUES( patent_PATENTS[Reference Number] ),
	"Tags",
		var __tags =
			CALCULATETABLE(
				VALUES( patent_PATENTS[Tag Name] ),
				patent_PATENTS[Tag Name] <> "no BRC",
				filter(
					ALL( patent_PATENTS[Tag Name] ),
					CONTAINSSTRING(
						patent_PATENTS[Tag Name],
						"BRC"
					)
				)
			)
		var __concat =
			CONCATENATEX(
				__tags,
				patent_PATENTS[Tag Name],
				", "
			)
		return
			__concat
)

But I don't quite get it... Are you creating a measure or you want a query? It looks like a query to me...

By the way, please do not use SUMMARIZE to do any calculations inside it. It's DANGEROUS and may return incorrect results without you even realizing that. Instead, use it only to get the unique combinations of values in rows.

Best
D

[D_PBI]

@Pragati11  - thanks for your contribution. Unfortunately, in following your example it returned nothing (empty column). I see the difference in your DAX to mine but it didn't work. Note, I had to remove the () bracket pairing from each filter line as without doing so it was returning the error 'Operator or expression '()' is not supported in this context'.

@Anonymous  - thanks for your contribution. It was spot on and worked how I needed.

To answer your question, assuming when you say measure you mean doing the piece of work using a DAX measure, and when you say query you are referring to completing the task using Power Query (is this what you mean??) - if you are suggesting I need a PQ query then yes, using Power Query, I would typically create an additional column in the table, that has the reference number/tag name, and concatenate the tag name there. I agree that this task being a modelling task it should be done in PQ.

However, I wanted to complete this requirement using DAX. I am strong at PQ but weaker at DAX. By requesting what I did, I have now seen some new stuff in DAX.
I placed your DAX code in a new table and then joined the new table's Reference Number field to the same field in patent_PATENTS table.

Kudos to both of you as efforts to my requirement, whether successful or not, are appreciated.

Thanks.

[Anonymous]

Well... No. When I say "measure," I mean a measure. When I say "query," I mean a query in DAX, which is a construct that starts with EVALUATE. Queries in DAX are the same concept as queries in MDX. PQ is a totally different animal still.

Queries are executed from a client that wants to pull out data from a tabular cube. It's like SQL to tables. For instance, the DAX function SUMMARIZECOLUMNS can only be used in queries (not in measures).

Best
D

[D_PBI]

In that case, a DAX 'query' is what I required for this exercise. A 'measure', being a scalar value, wouldn't cater for my intention.

Thanks again. Most helpful 👍

[Anonymous]

Yeah. I was kind of surprised when I saw that the initial formula returned a table... I couldn't be a measure, yet there was no EVALUATE. But then it could have been a calculated table as well. It's always good to state what kind of object you're after....

Best
D

[Pragati11]

Hi @D_PBI ,

Modify your DAX to the following:

Concatenate_Tags =
          SUMMARIZE(

                               Filter(

                                         (patent_PATENTS, patent_PATENTS[Tag Name] <> "no BRC")

                                         &&  

                                         (CONTAINS( patent_PATENTS , patent_PATENTS[Tag Name], "BRC" ) == TRUE())

                                       ),
                               patent_PATENTS[Reference Number],
                               "Tags", CONCATENATEX( VALUES( patent_PATENTS[Tag Name] ), patent_PATENTS[Tag Name], ", " )
                               )

If this helps please give Kudos and mark it as a Solution! 🙂

Thanks,

Pragati