cancel
Showing results for
Did you mean:  Helper II

## Get the first value in a filtered column

Hi all

I have two tables:

Sales

 Item Number Date Sales Amount Quantity Code A 01-01-2021 100 10 BRAVO A 01-01-2021 200 20 CHARLIE A 31-12-2020 300 30 ALPHA B 01-01-2021 500 5 BRAVO B 01-01-2021 400 4 CHARLIE

Items

 Item Number Description A Bike B Better Bike

I need a calculated column in my Items table which gives me the code of the highest sale in the last date possible.

So for item A the code would be CHARLIE. even though the 3rd row has a higher sale the date is older, so it's ruled out.

And for item B it would be BRAVO since it's the highest sale on the last possible date.

Any and all help is appreciated 🙂

1 ACCEPTED SOLUTION  Community Support

Hi @Hede92 ，

Try the following steps:

Step1,use the following measure:

``````test =
VAR test1 =
CALCULATE (
MAX ( 'Sales'[Date] ),
FILTER ( ALL ( 'Sales' ), 'Sales'[Item Number] = MAX ( 'Sales'[Item Number] ) )
)
VAR test2 =
CALCULATE (
MAX ( 'Sales'[Sales Amount] ),
FILTER (
ALL ( 'Sales' ),
'Sales'[Item Number] = MAX ( 'Sales'[Item Number] )
&& 'Sales'[Date] = test1
)
)
VAR test3 =
CALCULATE (
MAX ( 'Sales'[Code] ),
FILTER ( ALL ( 'Sales' ), 'Sales'[Sales Amount] = test2 )
)
RETURN
test3`````` Step2， new column base on the measure:

``codenew = [test]``

Final you will see the below: Wish it is helpful for you!

Best Regards

Lucien

4 REPLIES 4  Super User

Hi,

This should typically be solved via a measure in your visual.  Why do you want a calculated column formula in the input table?

Regards,
Ashish Mathur
http://www.ashishmathur.com  Community Support

Hi @Hede92 ，

Try the following steps:

Step1,use the following measure:

``````test =
VAR test1 =
CALCULATE (
MAX ( 'Sales'[Date] ),
FILTER ( ALL ( 'Sales' ), 'Sales'[Item Number] = MAX ( 'Sales'[Item Number] ) )
)
VAR test2 =
CALCULATE (
MAX ( 'Sales'[Sales Amount] ),
FILTER (
ALL ( 'Sales' ),
'Sales'[Item Number] = MAX ( 'Sales'[Item Number] )
&& 'Sales'[Date] = test1
)
)
VAR test3 =
CALCULATE (
MAX ( 'Sales'[Code] ),
FILTER ( ALL ( 'Sales' ), 'Sales'[Sales Amount] = test2 )
)
RETURN
test3`````` Step2， new column base on the measure:

``codenew = [test]``

Final you will see the below: Wish it is helpful for you!

Best Regards

Lucien  Super User

Hi, @Hede92

Please check the below picture and the sample pbix file's link down below. The Highest Sales in the lastest date CC =
VAR currentitem = Items[Item Number]
VAR latestdate =
MAXX ( FILTER ( Sales, Sales[Item Number] = currentitem ), Sales[Date] )
VAR highestsales =
MAXX (
FILTER ( Sales, Sales[Item Number] = currentitem && Sales[Date] = latestdate ),
Sales[Sales Amount]
)
VAR Codename =
MAXX (
FILTER (
Sales,
Sales[Item Number] = currentitem
&& Sales[Date] = latestdate
&& Sales[Sales Amount] = highestsales
),
Sales[Code]
)
RETURN
Codename

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.  Super User

@Hede92 , Create new columns like these in item table

max date = maxx(filter(sales, sales[item number] = item[item number]), sales[date])

max date sales = maxx(filter(sales, sales[item number] = item[item number] && sales[date] = item[max date]), sales[Sales Amount])

max date sales name = maxx(filter(sales, sales[item number] = item[item number] && sales[date] = item[max date] && sales[Sales Amount] = item[max date sales ] ), sales[Code])  