Concatenate dimension values
Hello,
Is there a way to concatenate two dimension values, like customer number and customer name, in a formula? As an example we have customer number "123" and customer name "ABC Appliances" and we want to concatenate them into "123 - ABC Appliances"
3 replies
-
What technologies are you using? Did you build the model via Pyramid's modeling?
-
We created the model on v2020. It's direct query against a SQL database. We tried something like [customer].[customer_id].CurrentMember+" - "+[customer].[customer_name].CurrentMember in a formula but it just gives us the value of the model's default measure.
-
The "formula" tool delivers semantic calculations - which roughly means calculations off the aggregated results - which is not what you are after. Its a complicated discussion, but you're looking to blend the 2 columns BEFORE you start querying. That is, at the grain.
The good news, is that it's super simple to do in the yellow modeling app:
- if the 2 columns are in the same table, you simply need to add a calculated column to the sequence. In the formula editor (use the fly-out editor with its drag and drop functions) build the exact same idea you had above, except its just the column names: [table].[column1]. The editor will help you do it. (see picture below)
- if they are in separate tables, use the Join tools first, to merge the 2 tables together and then do as described above.
