Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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] )
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |