cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## 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 10
Super User

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:

Helper II

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

Super User

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"
)``````

Super User

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"
)
)

Helper II

@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

Helper II

thanks, this is working, can you provide me M code also so i can make the custom column using query editor?

Super User

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,
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``````

Helper II

the above M code gives me the following error

Expression.Error: A cyclic reference was encountered during evaluation.
Super User

@zebra probably because you are trying to add new column with this code. Don't do that. Imagine you have your product table in PQ as separate query named "your_product_table". Then create a blank query with my code and see what happens. Check this video created by @ImkeF especially for you.

Super User

sorry, @zebra , here is video

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors