Forum Discussion
Creating custom column or measure
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 expandthe above M code gives me the following error
Expression.Error: A cyclic reference was encountered during evaluation.
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
