Forum Discussion

Krushnab85's avatar
Krushnab85
Helper I
10 months ago
Solved

Need 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 S...
Need Help
Show and Tell
Tips and Tricks
  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    10 months ago

    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


rohit1991's avatar
rohit1991
Super User
10 months ago

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:

 

 

Krushnab85's avatar
Krushnab85
Helper I
10 months ago

It worked for what i have provided but in real world secanario i need unique product names across the metric name groups - 
for example -

CCRectangle60Rectangle = 60
CCRectangle60Rectangle = 60
  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    10 months ago

    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