How to group by different values of each column

[o59393]

Hi all

I need to group by the different values of each different column. 

Basically go from this:

To this:

As seen in image above you have grouped by the values of type, country, sub region, ingredients supplier, ingredients, etc.

Is it possible to group by each individual column with its values?

Here is the pbix attached, the tab contains both the original table and desired result.

https://1drv.ms/u/s!ApgeWwGTKtFdh1w-lyDAzu8b9ubq?e=Srf9dfhttps://1drv.ms/u/s!ApgeWwGTKtFdh1v7b_9MidW...

Thanks!

[v-henryk-mstf · Accepted solution]

Hi @o59393 ,

According to your description, you can use the CONCATENATEX function to create multiple column formulas similar to the following, then create a virtual table, add the created column to it, and get the following result:

Col_Tpye = 
CALCULATE (
    CONCATENATEX ( VALUES ( 'Table'[Type] ), 'Table'[Type], "," ),ALL('Table')
)


Col_region = 
CALCULATE (
    CONCATENATEX ( VALUES ( 'Table'[Region]), 'Table'[Region], "," ),ALL('Table')
)

Col_Ingredienssulier = 
CALCULATE (
    CONCATENATEX ( VALUES ( 'Table'[Ingredientssupplier] ), 'Table'[Ingredientssupplier], "," ),ALL('Table')
)

Table_New = SUMMARIZE('Table',[Col_Tpye],[Col_region],[Col_Ingredienssulier])

related document link:

CONCATENATEX DAX function, Power BI String function - Power BI Docs

Hope to help you solve the problem.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[o59393]

Hi @amitchandak 

I used 

List of countries = CALCULATE(CONCATENATEX(DISTINCT('Table'[Country]),'Table'[Country],", "))

And worked for the matrix but not for the table:

How can I have it work in the table too?

Thanks.

[o59393]

Hi @amitchandak 

Thanks for the help. I tried the measure and look what happens:

How can I remove the duplicates?

Also want to know if this measure will group by each individual id or all the id's combined?

For example, ID 116 has Argentina, Brazil and Chile.

And let's say ID 117 has Ecuador and Colombia.

Will it do Argentina, Brazil, Chile, Ecuador and Colombia in one?

I would like to have it grouped by each id. Not by all combined, in this case Argentina, Brazil and Chile in one row and in another one Ecuador and Colombia. Is that possible?

Thanks!!!

[amitchandak]

@o59393 , You have create meausres using

Country measure = concatenatex(Table, [country], ",")

Country measure = concatenatex(Table, [sub region], ",")

same for others ans use then in visual

refer

https://docs.microsoft.com/en-us/dax/concatenatex-function-dax