Solved

Merge 3 text metrics into One

  • 17 January 2024
  • 5 replies
  • 189 views

Badge +2

Hello,
I have 3 text-type metrics with the same dimensions, and I would like to combine these 3 metrics into a single text-type metric.
In this new metric, I would like to add a category dimension to be assigned to each of the values from the original metrics.
Please let me know if I'm not clear enough!!
Thank you for your help :)
 

A basic example below :
 

 

 

icon

Best answer by Issam Moalla 17 January 2024, 18:04

View original

5 replies

Userlevel 5
Badge +9

Hi @Antoine-2 ,

To combine the three metrics, you would need to create a new metric with an additional dimension Category.

Category dimension

The Merge text formula will collect the text from each metric and attribute it to the corresponding Category item:

'Metric 1'[BY: Category."Metric 1"]
&
'Metric 2'[BY: Category."Metric 2"]
&
'Metric 3'[BY: Category."Metric 3"]

where:

  • Metric 1, Metric 2 and Metric 3 represent the text input metric.
  • [BY:  Category.”Metric x] is attributing each metric to the corresponding item within the Category dimension.
  • & operator is used to concatenate the text from each metric (there is no concatenation applied since each source metric is attributed to a different item.
    But our metrics have a text data type so this is the correct operator to use in order to merge.
Merge text

Hope this helps,
Issam

Badge +2

Hi @Issam Moalla ,

 

Thank you, this is exactly what I was looking for.
This leads me to another question, somewhat similar.
If I wanted to do exactly the same thing but instead of text metrics, these were dimension metrics. What would be the equivalent of "&" for concatenation ?

Thank you for your help :)

 

Userlevel 5
Badge +9

Hi @Antoine-2 ,

A possible way to apply this is by Applying the same logic but referring the unique text property in the Target metric: 

'Metric 1 (D)'.Name[BY: Category."Metric 1"]

and using the Item function in your formula to convert back to the dimension of your metric:
 

item('Metric 1 (D)'.Name[BY: Category."Metric 1"]
&
'Metric 2 (D)'.Name[BY: Category."Metric 2"]
&
'Metric 3 (D)'.Name[BY: Category."Metric 3"], ID100)

Where :

  • Metric 1, 2 and 3 have a data type dimension ID100
  • Metric x.Name: is giving as an output the Name property of ID100 dimension which is a text
  • Apply the same logic to allocate each metric to the corresponding item  and concatenate the metrics
  • Item function is converting the text back to a dimension based on the unique property.

However, keep in mind that each source metric (Metric 1, 2 and 3) must have the same dimension and the text property that you are using must be the unique property.

Hope this answers your request,
Issam

Badge +2

Hi @Issam Moalla ,

Thank you for your previous responses. I still have the same case, except that this time I want to do it for a "Boolean" type metric. However, when I try to apply the same concatenation logic with "&", it turns the boolean into text. Do you have any solutions to suggest on this matter?

Thank you in advance for your help.

Have a good day

Userlevel 5
Badge +9

Hi @Antoine-2 ,

When it comes to the booleans, you need to apply logical operators: AND/OR and your target metric needs to have a boolean data type.
Since you are looking to merge them by adding each metric to a category the OR would be the correct operator where at least one condition is met.
 

Hope this answers your question,
Issam

Reply