ow to group by and concatenate in a table

[Anonymous]

Hello all, 

I'm trying to replicate this table using dax queries and I cannot create them:

Product	count active substances	Origin
Apples	9	Brazil
7	Germany
6	Chile, France, Germany (3x) &Uruguay (2x)
Apricots	9	France
7	Greece
6	France (2x)
Blackberries	6	Serbia & unknown
Celery	8	France
6	France
Chilli	6	unknown
Chives	7	Germany
Cucumbers	6	Netherlands
Dill	8	Germany
Gherkins	8	Austria & Germany
7	Austria
Goose berries	6	Germany
Lemons	7	South Africa (2)
Lettuce	8	Germany
6	unknown

So far I got the following:
 
- Products
- Count of Active substances
- Origin

Each product has a country and Active substance. I got the next measure which concatenate the origin, but not the count of active substance.

Product Countries 6 =

IF(

    HASONEVALUE(Risk[Product]),

    CONCATENATEX(risk, "* " & Risk[Country Risk],

    UNICHAR(10)&UNICHAR(13) ,

    Risk[Country Risk]

    )

)

[Anonymous]

HI @Anonymous,

Any update on this? Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Greg_Deckler]

@Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.