cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hede92
Helper II
Helper II

Get the first value in a filtered column

Hi all

 

I have two tables:

Sales

Item NumberDateSales AmountQuantityCode
A01-01-202110010BRAVO
A01-01-202120020CHARLIE
A31-12-202030030ALPHA
B01-01-20215005BRAVO
B01-01-20214004CHARLIE

Items

Item NumberDescription
ABike
BBetter 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
v-luwang-msft
Community Support
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

v-luwang-msft_0-1623808905886.png

 

Step2, new column base on the measure:

codenew = [test]

Final you will see the below:

v-luwang-msft_1-1623808954818.png

 

Wish it is helpful for you!

 

Best Regards

Lucien

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/
v-luwang-msft
Community Support
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

v-luwang-msft_0-1623808905886.png

 

Step2, new column base on the measure:

codenew = [test]

Final you will see the below:

v-luwang-msft_1-1623808954818.png

 

Wish it is helpful for you!

 

Best Regards

Lucien

View solution in original post

Jihwan_Kim
Super User
Super User

Hi, @Hede92 

Please check the below picture and the sample pbix file's link down below.

 

Picture1.png

 

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

 

 

https://www.dropbox.com/s/m61oilv89jw63uq/hede92.pbix?dl=0 

 

 

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.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

amitchandak
Super User
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])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!