Forum Discussion

zebra's avatar
zebra
Helper II
2 years ago

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 ...
Need Help
FreemanZ's avatar
FreemanZ
Super User
2 years ago

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:

 

zebra's avatar
zebra
Helper II
2 years ago

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

  • FreemanZ's avatar
    FreemanZ
    Super User
    2 years ago

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