Forum Discussion

VikrantC's avatar
VikrantC
Icon for Helper I rankHelper I
4 years ago
Solved

Table Transformation with DAX

I have a Table (Table1) with date, product and price columns.  I calculated Monthly change as Table2 using a measure (Monthly%_Change) and created a new column called "Change".   I used the Table2 ...
  • Whitewater100's avatar
    Whitewater100
    4 years ago

    Hi Vikrant:

     

    Off the top of my head I can't see a way to do that, (I'm sure someone knows). Generally, the when the data model is  set up with fact and dim tables it allows for easier DAX and more complex analysis at the same time. Staying in one table can only take you so far.

     

    Sorry if this doesn't do the table answer you'd like.

     

    Here is one measure that produces the same results:(with the Date Table in Model)

    MOM % Avg Price 2 =
    var monthavg = AVERAGEX(VALUES(Dates[Month]),
    average(TableA[Price])
    )
    var PrevMontPrice = CALCULATE([Monthly Avg], PREVIOUSMONTH(Dates[Date]))
    var currprice = SELECTEDVALUE(TableA[Price])
    var pricediff = monthavg - PrevMontPrice
    var monumber = SELECTEDVALUE(Dates[Month No.])
    return
    IF( NOT(ISBLANK(currprice) && monumber >=2),
    DIVIDE(pricediff, [Prev Mont Price]))

     

    The Generate Table function might help here, not entirely sure...

  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi VikrantC 

    You may try

    Table3 =
ADDCOLUMNS (
    SUMMARIZE ( Table1, Calender[End Of Month] ),
    "Product A%", CALCULATE ( [Monthly%_Change], Table1[Product] = "Product A" ),
    "Product B%", CALCULATE ( [Monthly%_Change], Table1[Product] = "Product B" )
)
  • tamerj1's avatar
    tamerj1
    4 years ago

    Great VikrantC 

    If it solved your problem, would you please consider marking my reply as accepted solution?

VikrantC's avatar
VikrantC
Icon for Helper I rankHelper I
4 years ago

Hi,

I am able to do this.  However, I want to create a virtual Table3 so that I can use in other formulas.  Currently, I am taking the Table1 and I used a measure to created another table (Table2) to crate percentage.  I am transposing it in Table3.  Basically, I am trying to get to Table3 without creating Table2.  If you can please help it will be nice.  I  can transpose the columns but I trying to transpose with a calculation (measure).  Thanks.  

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi Vikrant:

     

    Off the top of my head I can't see a way to do that, (I'm sure someone knows). Generally, the when the data model is  set up with fact and dim tables it allows for easier DAX and more complex analysis at the same time. Staying in one table can only take you so far.

     

    Sorry if this doesn't do the table answer you'd like.

     

    Here is one measure that produces the same results:(with the Date Table in Model)

    MOM % Avg Price 2 =
    var monthavg = AVERAGEX(VALUES(Dates[Month]),
    average(TableA[Price])
    )
    var PrevMontPrice = CALCULATE([Monthly Avg], PREVIOUSMONTH(Dates[Date]))
    var currprice = SELECTEDVALUE(TableA[Price])
    var pricediff = monthavg - PrevMontPrice
    var monumber = SELECTEDVALUE(Dates[Month No.])
    return
    IF( NOT(ISBLANK(currprice) && monumber >=2),
    DIVIDE(pricediff, [Prev Mont Price]))

     

    The Generate Table function might help here, not entirely sure...