Forum Discussion
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?
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
4 Replies
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
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
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.
