Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
t_guet01
Helper I
Helper I

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 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:

 

ProductSellerSupplier
123HeadquarterX-Corp
123NationalEntity1X-Corp
123NationalEntity2Y-Corp
456HeadquarterNationalEntity2
456NationalEntity1W-Corp
456NationalEntity2Z-Corp

 

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

ProductSellerSupplier
123HeadquarterX-Corp
456NationalEntity2Z-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

1 ACCEPTED SOLUTION

Hi @t_guet01 ,

 

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

v-lionel-msft_0-1614674099747.png

 

v-lionel-msft_1-1614674112176.png

 

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.

View solution in original post

5 REPLIES 5
v-lionel-msft
Community Support
Community Support

Hi @t_guet01 ,

 

Please refer to my .pbix file.

v-lionel-msft_0-1613959915795.png

 

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.

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

Hi @t_guet01 ,

 

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

v-lionel-msft_0-1614674099747.png

 

v-lionel-msft_1-1614674112176.png

 

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
Super User
Super User

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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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?

t_guet01_0-1613727696167.png
(Unique Supplier for Product 456 should be Z-Corp instead of X-Corp)

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.