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?

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...

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

Hi Whitewhater100,

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

 

Regards,

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

      Hi Whitewater100,

      Thank you fro trying.  Basically, your results are same as my Table2.  What I am trying to do is not create the table2 but directly create Table3.  In my original post the Table3 is basically there are three columns:  Date, productA and ProductB.  The rows are MOM%.  I can do the price but NOT the MOM%.

       

      Again, thanks for you trying and helping.

      Regards,

      VC