Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!!
Im new on PBI and DAX, I want to know if there is a way to do this in dax, I have a table with many fields including the supplier, the material, the plant, the country and the unit price of products, I want to obtain the suppliers that sell the same material in different plants at a price different, in the same country.
I appreciate your help!
Mónica
Solved! Go to Solution.
Hi @Anonymous,
Please download the .pbix file(attachment) for more details.
Best Regards,
Angelia
Hi @Anonymous,
I test using your sample data and get expected result as follows.
1. Add a index column in Power query editor, please review more details from here.
2 Create a calculated columns based on index.
Price_Or = RANKX ( FILTER ( 'Sample', 'Sample'[Country] = EARLIER ( 'Sample'[Country] ) && 'Sample'[Material] = EARLIER ( 'Sample'[Material] ) && 'Sample'[Supplier] = EARLIER ( 'Sample'[Supplier] ) ), 'Sample'[Price], , ASC ) Plant_Or = RANKX ( FILTER ( 'Sample', 'Sample'[Country] = EARLIER ( 'Sample'[Country] ) && 'Sample'[Material] = EARLIER ( 'Sample'[Material] ) && 'Sample'[Supplier] = EARLIER ( 'Sample'[Supplier] ) ), 'Sample'[Plant], , ASC )
3. Create another two columns based on the columns above.
Price_tag = IF ( LOOKUPVALUE ( 'Sample'[Price_Or], 'Sample'[Index], 'Sample'[Index] + 1 ) <> 'Sample'[Plant_Or] || IF ( 'Sample'[Index] = 1, 'Sample'[Price_Or], LOOKUPVALUE ( 'Sample'[Price_Or], 'Sample'[Index], 'Sample'[Index] - 1 ) ) <> 'Sample'[Plant_Or], 1, 0 ) Plant_tag = IF ( LOOKUPVALUE ( 'Sample'[Plant_Or], 'Sample'[Index], 'Sample'[Index] + 1, 'Sample'[Country], 'Sample'[Country] ) <> 'Sample'[Plant_Or] || IF ( 'Sample'[Index] = 1, 'Sample'[Plant_Or], LOOKUPVALUE ( 'Sample'[Plant_Or], 'Sample'[Index], 'Sample'[Index] - 1, 'Sample'[Country], 'Sample'[Country] ) ) <> 'Sample'[Plant_Or], 1, 0 )
4. Then create a new table by clicking New table under Modeling on Home page, type the formula below and get right result.
Table = SELECTCOLUMNS ( FILTER ( 'Sample', 'Sample'[Price_tag] = 1 && 'Sample'[Plant_tag] = 1 ), "Country", 'Sample'[Country], "Po Number", 'Sample'[Po Number], "Po item", 'Sample'[Po item], "Supplier", 'Sample'[Supplier], "Material", 'Sample'[Material], "Plant", 'Sample'[Plant], "Price", 'Sample'[Price] )
Best Regards,
Angelia
Thanks for your help and your time,
I tried this solutions and i dont get the result that I hope.
Im get the same material, the same supplier, the same plant ; am going to tried chance a little this solution.
Thanks and greetings,
Monica
@Anonymouscan you provide sample daata set in excel sheet to get you the solution. Thanks
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks, im uploading the excel with a sample set; is asample, the original has a lot of columns.
https://drive.google.com/file/d/1NN6Mh85EnYPg8wByJ5REkZAdNNWWHT9r/view?usp=sharing
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |