DAX Aggregated and sorted data (from multiply rows)

[Anonymous]

Hallo,

I want to concatenate some columns and aggregate and sort them according to value from other column.

To better describe some example:

(I took the example from offical documentation site for SelectColumns https://msdn.microsoft.com/query-bi/dax/selectcolumns-function-dax)

For the following table named Info:

Country State Count Date Total

IND	JK	20	03.01.2010	800
IND	MH	25	01.01.2010	1000
IND	WB	10	02.01.2010	900
USA	CA	5	04.01.2010	500
USA	WA	10	05.01.2010	900

 I want to get following results. The states are aggregarted for each country, but sorted according to the date columne:

Country States

IND	MH; WB; JK
USA	CA;WA

I want that it works dynamicly so I cannot use here Power Query M or Calculated Columns. How can I achieve it with DAX ????

Many Thanx in advance

[Phil_Seamark · Accepted solution]

HI @Anonymous

Please try the following calculated measure and drag to a table visual along with your [Country] field.

Measure = 
    CONCATENATEX(
        'Table1',
        'Table1'[State],
        ";",
        'Table1'[Date]
        )

[Phil_Seamark · Accepted solution]

HI @Anonymous

Please try the following calculated measure and drag to a table visual along with your [Country] field.

Measure = 
    CONCATENATEX(
        'Table1',
        'Table1'[State],
        ";",
        'Table1'[Date]
        )

[Anonymous]

Thanks a lot 🙂 Maybe one more trivial question. I need the row that I calculate in that measure again in my source table:

Country State Count Date Total Aggregated

IND	JK	20	05.01.2010	800	MH;WB;JK
IND	MH	25	01.01.2010	1000	MH;WB;JK
IND	WB	10	02.01.2010	900	MH;WB;JK
USA	CA	5	04.01.2010	500	CA;WA
USA	WA	10	05.01.2010	900	CA;WA

That is what I want to achieve. Additionally if I for example add A slicer for dates 01.01.2010 - 05.01.2010

If I choose the slicer 01.01.2010 - 04.01.2010, the table should look like that:

Country State Count Date Total Aggregated

IND	MH	25	01.01.2010	1000	MH;WB
IND	WB	10	02.01.2010	900	MH;WB
USA	CA	5	04.01.2010	500	CA

How to achieve that , I tried to add ALL to my measure but then I get all the States in one row, no matter which country it is or if there are any other filters used. @Phil_Seamark Do you know how to solve this problem ??? Many thanks in advance for your help.

[Phil_Seamark · Accepted solution]

Hi @Anonymous

If you want the value in your source table, please try the following calculated column

New Column = 
    CONCATENATEX(
        FILTER('Table1','Table1'[Country] = EARLIER('Table1'[Country])
        ) ,
        'Table1'[State] ,
        ";" ,
        Table1[Total]
    )

However this will not respect slicers.  If you want a dynamic calculation to respect slicers, please use the measure I posted inititally.