Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |