Forum Discussion
Creating custom column or measure
Hey All,
I am having hard time in creating custom column/measure according to following scnario. Suppose I have product table as follows
| Id | parent_id | Name | Price | Updated_At |
| 1 | null | p1 | 100 | 13/10/2023 |
| 2 | 1 | p1 | 150 | 14/10/2023 |
| 3 | null | p2 | 200 | 15/10/2023 |
| 4 | null | p3 | 100 | 10/10/2023 |
| 5 | 4 | p3 | 50 | 11/10/2023 |
| 6 | 4 | p3 | 70 | 12/10/2023 |
The Product table has products with its various revision according to price change. I want to make another column thats shows the trend. The final output would be as follows.
| Id | parent_id | Name | Price | Updated_At | Trend |
| 1 | null | p1 | 100 | 13/10/2023 | increasing |
| 2 | 1 | p1 | 150 | 14/10/2023 | |
| 3 | null | p2 | 200 | 15/10/2023 | unchanged |
| 4 | null | p3 | 100 | 10/10/2023 | increasing |
| 5 | 4 | p3 | 50 | 11/10/2023 | |
| 6 | 4 | p3 | 70 | 12/10/2023 |
Here you can see that for p1, we have its price changed in 2nd row so the trend is increasing. for p2, there are no revisions so trens is unchanged and for p3, we have two variations and here we need to compare last two and according to it, price is increasing so we have increasing trend.
Would anyone help me in calculating these using custom columns?
Thanks
zeebee
10 Replies
This code adds a calculated column
Trend =
VAR _tbl =
FILTER (
SUMMARIZE (
'DataTable',
'DataTable'[Name],
'DataTable'[Price],
'DataTable'[Updated_At]
),
'DataTable'[Name] = EARLIER ( 'DataTable'[Name] )
)
VAR _dif =
MAXX (
INDEX ( -1, _tbl, ORDERBY ( 'DataTable'[Updated_At] ) ),
'DataTable'[Price]
)
- MAXX (
INDEX ( -2, _tbl, ORDERBY ( 'DataTable'[Updated_At] ) ),
'DataTable'[Price]
)
RETURN
IF (
ISBLANK ( 'DataTable'[parent_id] ),
SWITCH (
TRUE (),
OR ( COUNTROWS ( _tbl ) = 1, _dif = 0 ), "Uncahged",
_dif > 0, "Increasing",
"Decreasing"
)
)thanks, this is working, can you provide me M code also so i can make the custom column using query editor?
HI, zebra here goes M
let Source = your_product_table, values = {"decreasing", "unchanged", "increasing"}, f = (tbl as table) as table => [prices = List.LastN(tbl[Price], 2), comp = Value.Compare(prices{1}? ?? prices{0}, prices{0}) + 1, add = Table.AddColumn(tbl, "Trend", (x) => if x[parent_id] = null then values{comp} else null)][add], group = Table.Group(Source, {"Name"}, {{"all", each f(Table.Sort(_, "Updated_At"))}}), expand = Table.ExpandTableColumn(group, "all", {"Id", "parent_id", "Price", "Updated_At", "Trend"}) in expand
wdx223_Daniel , The above solution works however It is not working in the following scenario. I have another type of input table as follows. In this, I need to calculate trend based on step, there are finite number of steps lets say step1, step2, step3 and step 4. and if step is changing, it is either increasing or decreasing and if there is only one row then it is unchanged.
Id parent_id other columns step Updated_At 1 null … step 1 13/10/2023 2 1 … step 2 14/10/2023 3 null … step 1 15/10/2023 4 null … step 1 10/10/2023 5 4 … step 2 11/10/2023 6 4 … step 1 12/10/2023 and I want to have trend according to steps as follows
Id parent_id other columns step Updated_At Trend 1 null … step 1 13/10/2023 step increasing 2 1 … step 2 14/10/2023 3 null … step 1 15/10/2023 unchanged 4 null … step 1 10/10/2023 5 4 … step 2 11/10/2023 step increasing 6 4 … step 1 12/10/2023 Moroever, please provide the explanation of the code as I am new to this tool and unable to understand how you are calculating. I try to use dax studio to see the output of the intermediate steps however dax studio throwing errors as shown below.
So it will be great if you explain the steps and also let me know how i can see the output of intermediate steps?
thanks
zebee
hi zebra ,
you may try to plot a table visual with the name column and a measure like:
measure = VAR _table = FILTER(data,data[Name]=MAX(data[Name])) VAR lastprice = MAXX( TOPN(1,_table, data[Updated_At]), data[Price] ) VAR llastprice= MAXX( EXCEPT( TOPN(2, _table, data[Updated_At]), TOPN(1, _table, data[Updated_At]) ), data[Price] ) RETURN SWITCH( TRUE(), llastprice = BLANK() || lastprice=llastprice, "Unchanged", lastprice>llastprice, "Increasing", "decreasing" )it worked like:
This is not the correct solution as it gives me the following output when I added the data as follows
I need a measure/column that gives the trend in the orignal data format
hi zebra ,
if you need to bring all the columns into the visual, tweak the code like:
measure = VAR _table = FILTER(ALL(data),data[Name]=MAX(data[Name])) VAR lastprice = MAXX( TOPN(1,_table, data[Updated_At]), data[Price] ) VAR llastprice= MAXX( EXCEPT( TOPN(2, _table, data[Updated_At]), TOPN(1, _table, data[Updated_At]) ), data[Price] ) RETURN SWITCH( TRUE(), llastprice = BLANK() || lastprice=llastprice, "Unchanged", lastprice>llastprice, "Increasing", "decreasing" )
