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 NameProductSampleRequired Output
AATriangle10Triangle=10, Square = 20
AASquare20Triangle=10, Square = 20
BBTriangle30Triangle=30, Square = 40, Circle = 50
BBSquare40Triangle=30, Square = 40, Circle = 50
BBCircle50Triangle=30, Square = 50, Circle = 50
CCRectangle60Rectangle = 60, Triangle = 70
CCTriangle70Rectangle = 60, Triangle = 70

 

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


7 Replies

  • 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


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

    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.

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

    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's avatar
    srlabhe
    Super User
    10 months ago

    Try below DAX 

    Products Concatenated =
    VAR AllProducts =
    CALCULATETABLE(
    VALUES('Products'[Product Name]),
    ALL('Metrics')
    )
    RETURN
    CONCATENATEX(
    AllProducts,
    'Products'[Product Name],
    ", "
    )

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

    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