Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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.
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.
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.
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 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)
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)
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |