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.
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 =
However, this is not working. Please can advise help?
Thanks.
Solved! Go to 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
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
@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.
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 👍
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |