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

[t_guet01]

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 Power BI file with data. The data are connected to the clients servers, which we are unable to access. So we are only able to work on the surface with DAX - no data transformation in Query M possible. (since we cannot see any data in the query editor view)

We got a table which looks like this:

Product	Seller	Supplier
123	Headquarter	X-Corp
123	NationalEntity1	X-Corp
123	NationalEntity2	Y-Corp
456	Headquarter	NationalEntity2
456	NationalEntity1	W-Corp
456	NationalEntity2	Z-Corp

What I am expecting to create is a table looking like this:

Product	Seller	Supplier
123	Headquarter	X-Corp
456	NationalEntity2	Z-Corp

The logic is:

IF supplier from seller "Headquarter" is not equal to "NationalEntity1" or "NationalEntity2" (so the product is directly purchased by the headquarter) THEN take the supplier (here X-Corp) of seller "Headquarter"

ELSEIF supplier from seller "Headquarter" is either "NationalEntity1" or "NationalEntity2" (so the product is purchased by a national entity) THEN take the supplier of the respective national entity which supplies the headquarter

I know the Group By function in DAX, however, I only used it to summarize values by SUMX. How do I deal with text-values with a decision logic behind?

Any ideas?

Your help is much appreciated.

Best

Till

[v-lionel-msft]

Hi @t_guet01 ,

Please refer to my .pbix file.

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.

[t_guet01]

Dear @v-lionel-msft ,

thank you very much for your reply. 

My original data is much bigger, which however I am not able to share to protect my clients data. When adding a third product, say 789, with Headquarter Supplier NationalEntity1, your Measure 2 starts failing, since it says "NationalEntity2" for each row.

Do you have any solution for that?

Thank you very much.

Best

Till

[v-lionel-msft · Accepted solution]

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]

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

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)