Forum Discussion

t_guet01's avatar
t_guet01
Icon for Helper I rankHelper I
5 years ago
Solved

How do I use Group By in DAX with text values?

Hey guys,   I want to Group By products in DAX and add columns with text value. The products have a unique ID, however the additional columns are not unique.    Setting: Our client has sent us a ...
Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    5 years ago

    Hi t_guet01 ,

     

    I have found the problem. Measure 2 is not grouped by product. Please refer to my .pbix file again.

     

     

    Best regards,
    Lionel Chen

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

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

t_guet01  You should be able to create a measure that either uses IF or SWITCH like:

Measure Supplier =
  VAR __Product = MAX('Table'[Product])
  VAR __HQSupplier = MAXX(FILTER('Table',[Seller]="Headquarter"),[Supplier])
RETURN
  IF(__HQSupplier IN SELECTCOLUMNS('Table',"__Supplier",[Seller]),MAXX(FILTER('Table',[Seller]=__HQSupplier),[Supplier]),__HQSupplier)
t_guet01's avatar
t_guet01
Icon for Helper I rankHelper I
5 years ago

Dear Greg_Deckler  ,

thanks a lot for your reply.

I think I understand the basic idea behind your formula apart from:

- the "IN SELECTCOLUMNS" expression checks whether the HQ-Supplier is also a seller, right?

- why you define the "___Product" variable since you do not use it later on in the RETURN part?

- MAX and MAXX are not "really" looking for the maximum value, just extracting a product number, right?

I tried to implement your formula. What am I doing wrong?


(Unique Supplier for Product 456 should be Z-Corp instead of X-Corp)