Forum Discussion
DAX Aggregated and sorted data (from multiply rows)
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:
| 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
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] )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.
3 Replies
- Phil_Seamark
Microsoft Employee
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] )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
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.