Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Combine Column

Hi good day, pls can anyone help me on my table how can i get the result i required.   DESIRED OUTPUT   Thank you
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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] ) ) )

     

gmsamborn's avatar
gmsamborn
Super User
1 year ago

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?

 

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago

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.

  • gmsamborn's avatar
    gmsamborn
    Super User
    1 year ago

    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.