topic Re: DAX Command to merge multiple Columns into One column in DAX Commands and Tips

DAX Command to merge multiple Columns into One column

Anonymous — Thu, 18 Jul 2019 22:11:54 GMT

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

Re: DAX Command to merge multiple Columns into One column

HotChilli — Fri, 19 Jul 2019 00:21:01 GMT

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

Add a conditional column

Re: DAX Command to merge multiple Columns into One column

Anonymous — Fri, 19 Jul 2019 08:53:23 GMT

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

Re: DAX Command to merge multiple Columns into One column

Anonymous — Thu, 25 Jul 2019 03:55:03 GMT

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,

Re: DAX Command to merge multiple Columns into One column

Anonymous — Thu, 01 Aug 2019 17:35:32 GMT

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