Forum Discussion
Combine Column
Hi AllanBerces
While this is very possible in DAX, the calculation won't update with new columns as each column has to be manually referenced and if a colum that's been referenced is deleted, the calc table will return an error.
UnpivotDAX = VAR _job = DISTINCT ( SourceTable[Job] ) VAR _jobyr = CROSSJOIN ( _job, SELECTCOLUMNS ( { 2024, 2025 }, "Year", [Value] ) ) VAR _earned = ADDCOLUMNS ( _jobyr, "Earned", VAR _job = [Job] VAR _2024 = SUMX ( FILTER ( SourceTable, [Job] = _job ), [Earned_2024] ) VAR _2025 = SUMX ( FILTER ( SourceTable, [Job] = _job ), [Earned_2025] ) RETURN SWITCH ( [Year], 2024, _2024, 2025, _2025 ) ) RETURN FILTER ( _earned, NOT ( ISBLANK ( [Earned] ) ) )
Hi AllanBerces
You can do this very easily using Power Query.
What I did was make a reference to the Source Table, select the Job column and then ‘Unpivot other columns’.
After renaming the Attribute column to Year and removing "Earned_" from the Year column by using "Replace Values" and changed the type, your data should be in a usable format for DAX.
Let me know if you have any questions.
Hi gmsamborn thank you for the reply, but my table come from summarized table
Hi AllanBerces
I said it'd be easy in Power Query. DAX is a different question.
I'm curious how you built the summarized table to have that format. (Also, the format of the data you are summarizing.)
Would it be possible to remove "Earned_" from the column headers of your summary table?
Hi gmsamborn thank you very much for the reply, I understand its easy on the PQ if i used only one table but my on my case. I summarized one of my table then lookup on the other table to get Earned per year and that my current table that i required desired output.
OK AllanBerces
It might be possible if the column headers didn't have "Earned_".
Sorry I couldn't help and I'm going to keep an eye on this question since I would like to see the DAX. I'm sure others will find something that helps.