Forum Discussion

newpbiuser01's avatar
newpbiuser01
Helper V
3 years ago
Solved

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...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Community Support
3 years ago

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's avatar
    newpbiuser01
    Helper V
    3 years ago

    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's avatar
      v-yueyunzh-msft
      Community Support
      3 years ago

      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