Filtering Text Value based on criteria

[newpbiuser01]

Hello,

I have a table similar to the one below:

Vendor	Product	Year	Month
Vendor1	A	2022	11
Vendor2	B	2023	1
Vendor1	C	2022	12
Vendor2	D	2022	12
Vendor3	E	2023	2

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?

[Ashish_Mathur]

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.

[v-yueyunzh-msft · Accepted solution]

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

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

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

[newpbiuser01]

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!

[v-yueyunzh-msft]

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