Skip to main content
cancel
Showing results for 
Search instead 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

Reply
newpbiuser01
Helper IV
Helper IV

Filtering Text Value based on criteria

Hello,

I have a table similar to the one below:

VendorProductYearMonth
Vendor1A202211
Vendor2B20231
Vendor1C202212
Vendor2D202212
Vendor3E20232

For each of the vendors, I need to find the latest data I have, and then retrieve the Product in that year. I created a concatenated column for Year-Month to get the latest date I have for the vendor. However where I'm stuck is how to retrieve/lookup the product based on that. If it were a number where I was calculating a sum, I'd say Calculate(sum(Product), filter(table, [year] = max([year]) && [month] = calculate(max([month], filter(table, [year] = max([year]))))). But this is a text string. How do I do find the product string using the filtering criteria?

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

Hi, @newpbiuser01 

According to your description, you want to "For each of the vendors, I need to find the latest data I have, and then retrieve the Product in that year. ".

Thanks for your sample data first!
Here are the steps you can refer to :
We can click "New Column" to create a calcualted column :

Year_Month = [Year]*100+[Month]

 

[1]If you want to create a calcualted column  to get the Product , you can use this dax:

Column = var _vendor = [Vendor]
var _max_date =MAXX( FILTER('Table' , 'Table'[Vendor] = _vendor ) , [Year_Month])
return
MAXX(FILTER('Table','Table'[Vendor] = _vendor && 'Table'[Year_Month]  =_max_date ) , [Product])

vyueyunzhmsft_0-1683682423771.png

[2]If you want to create a measure  to get the Product , you can use this dax:

Measure = var _vendor =MAX('Table'[Vendor])
var _max_date =MAXX( FILTER('Table' , 'Table'[Vendor] = _vendor ) , [Year_Month])
return
MAXX(FILTER('Table','Table'[Vendor] = _vendor && 'Table'[Year_Month]  =_max_date ) , [Product])

vyueyunzhmsft_1-1683682454627.png

 

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

Do you want a measure or a calculated column formula solution?  If you want a calculated column formula solution, then show the expected result in a spare column of the table that you have shared above.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yueyunzh-msft
Community Support
Community Support

Hi, @newpbiuser01 

According to your description, you want to "For each of the vendors, I need to find the latest data I have, and then retrieve the Product in that year. ".

Thanks for your sample data first!
Here are the steps you can refer to :
We can click "New Column" to create a calcualted column :

Year_Month = [Year]*100+[Month]

 

[1]If you want to create a calcualted column  to get the Product , you can use this dax:

Column = var _vendor = [Vendor]
var _max_date =MAXX( FILTER('Table' , 'Table'[Vendor] = _vendor ) , [Year_Month])
return
MAXX(FILTER('Table','Table'[Vendor] = _vendor && 'Table'[Year_Month]  =_max_date ) , [Product])

vyueyunzhmsft_0-1683682423771.png

[2]If you want to create a measure  to get the Product , you can use this dax:

Measure = var _vendor =MAX('Table'[Vendor])
var _max_date =MAXX( FILTER('Table' , 'Table'[Vendor] = _vendor ) , [Year_Month])
return
MAXX(FILTER('Table','Table'[Vendor] = _vendor && 'Table'[Year_Month]  =_max_date ) , [Product])

vyueyunzhmsft_1-1683682454627.png

 

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hi @v-yueyunzh-msft,

 

That works! But question about this - why use the MAXX function for a string value? Is there a function to aggregate the string/text values do you know ( like a distinct concatenate?)

 

Thank you for all your help!

Hi , @newpbiuser01 

Sure , the SUM() function is used for the number type , and the MAX() or the MIN() can be used for the number type and Text type . So i think if you want to get the text type value , you can just use this.

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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