Append 2 calculated columns using DAX

[DataVitalizer]

Hi Community,

• Sample data: Table 1 which I am currently using contains values per Month/Year for each IND L2, as you can see the first 3 columns are might be used a hierarchy.
• Target (Table2):
  • Represents the SUM of the previous year (I will add Previous month later) according to a selected Month&Year
  • in this case the both columns IND L1 and IND L2 must be appended in one column
    IND    | Values 
    IND_1 | value
    IND_2 | sum(IND_3 & IND_4)
    IND_3 | value
    IND_4 | value

Calculating the Previous Year values and Previous Month values for each IND L2 was perfectly done, and using a matrix visual combined with a hierarchy I was able also to show the same thing either for IND L1 or IND L2.

=> Currently I am stuck in finding a way to append the both columns in one and maintaining the same calculations, I'd appreciate any suggestions you may have to move from Table1 to Table 2.

Thank you in advance Community.

[lbendlin]

please provide the sample data in usable format, not as a screenshot.

[DataVitalizer]

Hi @lbendlin
Here is a sample:

Rubric,IND L1,IND L2,Date,Value
R1,IND_1,IND_1,1/1/2017,100
R1,IND_1,IND_1,1/1/2018,150
R1,IND_1,IND_1,1/1/2019,200
R1,IND_2,IND_3,1/1/2017,250
R1,IND_2,IND_3,1/1/2018,300
R1,IND_2,IND_3,1/1/2019,350
R1,IND_2,IND_4,1/1/2017,400
R1,IND_2,IND_4,1/1/2018,450
R1,IND_2,IND_4,1/1/2019,500
R2,IND_5,IND_6,1/1/2018,550
R2,IND_5,IND_6,1/1/2019,600
R2,IND_5,IND_7,1/1/2018,650
R2,IND_5,IND_7,1/1/2019,700
R2,IND_8,IND_8,1/1/2017,750
R2,IND_8,IND_8,1/1/2018,800

Thank you in advance
@Greg_Deckler @amitchandak

[lbendlin]

Unless I am missing some subtlety here your hierarchy doesn't really matter.  Any aggregations you do in your visual will produce the desired results.

[DataVitalizer]

Hi @lbendlin

I achieved that view previously but what I am supposed to do now is appending the two columns (IND_L1, IND_L2) in one column.

Thank you in advance.

[lbendlin]

You can create a new calculated column that concatenates  your text columns.

IND = [IND L1] & "|" % [IND L2]

but why?  I still don't understand what you are trying to achieve.

[DataVitalizer]

Hi @lbendlin 

Appending two columns in one

Thank you in advance.

[v-xicai]

Hi @DataVitalizer ,

Got it. While it is impossible to achieve your requirement completely, which put two columns into single column as the layout you showed. As a workaround, you may put [Rubric], [IND L1] and  [IND L2] into Rows box Matrix visual, put the [Date] and [Value PY] into Values box.

To get the [Value PY], you may create calendar table firstly, then create measure like DAX below.

Calculated table:

Calendar= CALENDARAUTO()



Measure:

Value PY = CALCULATE(SUM(Table1[Value]),SAMEPERIODLASTYEAR(Calendar[Date]))

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataVitalizer · Accepted solution]

Hi @v-xicai 

Thank you for your replay.
The first solution I thought about was using a matrix by it didn't answer my need which was appending those 2 columns in one new column.

After hours I acheived the result I was looking for by using the function UNION

Thank you community.