Forum Discussion
Helper INeed help on creating calculated column
Hello Guys, Need a help for creating a calculated column as below "Required Output" Column
| Metric Name | Product | Sample | Required Output |
| AA | Triangle | 10 | Triangle=10, Square = 20 |
| AA | Square | 20 | Triangle=10, Square = 20 |
| BB | Triangle | 30 | Triangle=30, Square = 40, Circle = 50 |
| BB | Square | 40 | Triangle=30, Square = 40, Circle = 50 |
| BB | Circle | 50 | Triangle=30, Square = 50, Circle = 50 |
| CC | Rectangle | 60 | Rectangle = 60, Triangle = 70 |
| CC | Triangle | 70 | Rectangle = 60, Triangle = 70 |
Hi Krushnab85 ,
Please wrap your table filter inside a Distinct function, this way you will get the desired output.Required Output 2 = VAR MetricGroup = 'Table'[Metric Name] RETURN CONCATENATEX ( DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Table', 'Table'[Metric Name] = MetricGroup ), "Product", 'Table'[Product], "Sample", 'Table'[Sample] ) ), [Product] & " = " & [Sample], ", " )Uploading the sample .pbix file for reference.
I hope this information helps. Please do let us know if you have any further queries.
Thank you
7 Replies
rohit1991Super User
Hi Krushnab85
1. Create the Calculated Column.We want a column that shows all Product = Sample values for each Metric Name group.
- In Data view, go to Modeling >> New column.
- Enter this DAX:
Required Output = VAR MetricGroup = 'Table'[Metric Name] RETURN CONCATENATEX ( FILTER ( 'Table', 'Table'[Metric Name] = MetricGroup ), 'Table'[Product] & " = " & 'Table'[Sample], ", " )2. Outcome:
Krushnab85It worked for what i have provided but in real world secanario i need unique product names across the metric name groups -
for example -CC Rectangle 60 Rectangle = 60 CC Rectangle 60 Rectangle = 60 Hi Krushnab85 ,
Please wrap your table filter inside a Distinct function, this way you will get the desired output.Required Output 2 = VAR MetricGroup = 'Table'[Metric Name] RETURN CONCATENATEX ( DISTINCT ( SELECTCOLUMNS ( FILTER ( 'Table', 'Table'[Metric Name] = MetricGroup ), "Product", 'Table'[Product], "Sample", 'Table'[Sample] ) ), [Product] & " = " & [Sample], ", " )Uploading the sample .pbix file for reference.
I hope this information helps. Please do let us know if you have any further queries.
Thank you
Hi Krushnab85
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.Hi Krushnab85
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank you- srlabhe
Try below DAX
Products Concatenated =
VAR AllProducts =
CALCULATETABLE(
VALUES('Products'[Product Name]),
ALL('Metrics')
)
RETURN
CONCATENATEX(
AllProducts,
'Products'[Product Name],
", "
) Hi Krushnab85
As we haven’t heard back from you, we wanted to kindly follow up to check if the suggestions provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.
Thanks and regards