DAX Command to merge multiple Columns into One column

[Anonymous]

Hi,

Lets picture a table structure as this:

Month        Budget_Q1       Budget_Q2     Budget_Q3       Budget_Q4

----------------------------------------------------------------------------

Jan               100                  .....                    .......                      .......

Feb              100                   .....                   ......                        ........

Mar              100                  .....                   ........                       .......

Apr               110                  115                 .......                        .......

May              110                  115                 ......                        .......

Jun               110                  115                 ........                      .......

Jul                120                  122                 125                       ......

Aug              120                  122                 125                      ......

Sep               120                  122                 125                      .....

Oct               110                   115                120                      115

Nov              110                   115                 120                     115

Dec               110                   115                 120                     115

...

I'm trying to merge a selection of Rows to get the original budget for that quarter. That means the table should look like

Month            Budget_Summary

-------------------------------------

Jan                   100 (From Column Budget_Q1)

Feb                   100 (From Column Budget_Q1)

Mar                   100 (From Column Budget_Q1)

Apr                    115 (From Column Budget_Q2)

May                   115 (From Column Budget_Q2)

Jun                     115 (From Column Budget_Q2)

Jul                     125 (From Column Budget_Q3)

Aug                  125 (From Column Budget_Q3)

Sep                   125 (From Column Budget_Q3)

Oct                   115 (From Column Budget_Q4)

Nov                   115 (From Column Budget_Q4)

Dec                   115 (From Column Budget_Q4)

This is basically an equivalant to SQL code below:

SELECT BUDGET_Q1

FROM table_x

WHERE MONTH IN ("Jan","Feb","Mar")

UNION 

SELECT BUDGET_Q2

FROM table_x

WHERE MONTH IN ("Apr","May","Jun")

UNION 

SELECT BUDGET_Q3

FROM table_x

WHERE MONTH IN ("Jul","Aug","Sep")

UNION 

SELECT BUDGET_Q4

FROM table_x

WHERE MONTH IN ("Oct","Nov","Dec")

How may I achieve this in DAX?

Thanks,

Usher

[Anonymous]

Hi there.

Mate, DAX is not a tool for reshaping data. It's a CALCULATION tool only. To do what you want is a job for Power Query. If you want to have an easy time with Power BI, please learn to use the right tool for the job.

Also, I'd advise you to use the Unpivot tool from the ribbon of PQ instead of following the convoluted advice from @HotChilli. If you use Unpivot, you won't have to type a single character.

Best

Darek

[Anonymous]

Mate @Anonymous ,

Thanks for clarifying. You are right. DAX is not the right tool for reshaping the data. 

It never hurts asking and exploring other solutions though. You just confirmed it. #thewayswelearn

I ended up reshaping my data in PQ using M on a row level basis.

Also thanks to @HotChilli for the attempt to answer the question. 

I appreciate that.

Cheers,

Usher

[HotChilli]

If you want to do it easily, do it in Power Query.

Add a conditional column

[Anonymous]

@HotChilli 

Thanks for the response but the solution I'm after is more like a row based condition where, imagine today as the split in time between past and future. For the sake of example lets say, past is Q1, Q2 and future is Q3 and Q4. Data is tabular  and each Qtr has it's own column. So there are 4 columns for Q1,Q2,Q3,Q4.

At this point in Time (Past=Q1, Q2, Future=Q3,Q4) We need to get the numbers partly from Q1 And Q2 for the past dates and partly from Q3 and Q4 for future periods. This means either a new measure or new column where all 4 qtr's are consolidated via a rule. With what you proposed is either or or each column. What I need is a combination of coulmns. 

I did a bit of try and error and couldn't find the solution to be handled via measures. easiest seems to be a new column with a formula to determine the rule is applied at a granular level on each row. It effects the performance though.

Column formule is something like

"Current Budget = SWITCH(True(),

                                                  AND(Month<SYSDATE(),Month in Q1), Q1_Budget,

                                                  AND(Month<SYSDATE(),Month in Q2), Q2_Budget,

                                                  AND(Month<SYSDATE(),Month in Q3), Q3_Budget,

                                                  AND(Month<SYSDATE(),Month in Q4), Q4_Budget,

                                                  Month>SYSDATE(), Budget of CURRENT_MONTH_QUARTER

                                   )

Does that make more sense?

Thanks,