cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper IV

## Filtering Text Value based on criteria

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?

1 ACCEPTED SOLUTION
Community Support

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

4 REPLIES 4
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
Community Support

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

Helper IV

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!

Community Support

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