topic Check Pricelist to another pricelist on item in DAX Commands and Tips

Check Pricelist to another pricelist on item

SvenvandenHil — Mon, 29 Jul 2024 12:28:00 GMT

I have a table with itemnumbers and there prices. Only there are multiple pricelists. In below PowerBI file there is an example for 6 of those pricelists on itemnumber level. I want to check 1 pricelist to another pricelist and give a percentage difference.

TestPrices

I made 2 distinct tables to use in a filter visual. And made 2 DAX formulas to create a price for both filter tables. But the DAX formulas now also filter on both filter visuals. But what I want to achieve is that if I use filter 1, the column PriceDim1 changes and PriceDim2 will be zero. And if I use filter 2, PriceDim2 changes and PriceDim1 is zero. So that I can see the differences between 2 pricelists. And the next thing would be nice is to calculate this difference in absolute and percentages amounts, in an extra column.

Re: Check Pricelist to another pricelist on item

AlanFredes — Mon, 29 Jul 2024 14:00:28 GMT

Hi SvenvandenHil ,

Given how filtering context works in Power and the current relationship setup you have in the model it isn't possible to build what you are looking for.

But Power BI has a DAX function that allows you to filter (or relate) a table without using relationships through the TREATAS function.

You would need to break the relationships you built and add in TRETAS to the DAX measures you created. There will also be a restriction in the PriceCustomerGroups you filter forcing you to always have one selected.

I made the changes in your file and you can find the updated version in the bellow link.

20240729 TestPrices Alan Fredes

Let me know if you have any questions.

Regards,
Alan Fredes

Did I answer your question? Mark my post as a solution!

Re: Check Pricelist to another pricelist on item

SvenvandenHil — Mon, 29 Jul 2024 14:19:13 GMT

Hi Alan,

Thank you, worked like a charm.

Kind regards,

Sven