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

Announcements

#### Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

#### Power BI March 2023 Update

Find out more about the March 2023 update.

#### March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors