Forum Discussion

VikrantC's avatar
VikrantC
Helper 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
    Helper 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
      Solution 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...

      • VikrantC's avatar
        VikrantC
        Helper I
        4 years ago

        Hi Whitewhater100,

        Thank you very much.  Unfortunately, it did not work.

         

        Regards,