topic Re: Link supplier to corresponding products but in terms of sales quantities in DAX Commands and Tips

Link supplier to corresponding products but in terms of sales quantities

George1973 — Tue, 30 Nov 2021 12:09:23 GMT

Hi All,

here is the problem.. I have a classic relations between Clients/Vendors another enviroment:

When I want to operate with the GOOGS supplied filtered with some specific suppliers, the relations works perfect and I can do all other calculation.

But when it comes to calculate some sales qualitites filtered by specific suppliers (supposing that I want to get sales for that supplier only) then the relations given above does not work. It works only if "Client_ID" plays role in "Operations" table as a Buyer..

I know that it's a obvious error.. But how to overcome it with DAX, I do not know.. Please advise ASAP 🙂

Re: Link supplier to corresponding products but in terms of sales quantities

George1973 — Tue, 30 Nov 2021 12:22:32 GMT

One of the possible solution comming to mind is - To create a calculated column in the Table "GOODS" showing related supplier and based on it filter the sales performance. But, I do not know how to do it and secondly - It seems to me somehow artificial

Re: Link supplier to corresponding products but in terms of sales quantities

George1973 — Tue, 30 Nov 2021 13:41:10 GMT

Well,
I did it in very "artificial way":
1. Created a separate table summirizing only Import operations from "Operations" table with the fields "Operation ID" and "Vendor/Client_ID"
2. Created a calculated column in "Operations Details" with ValueLookup ralating the vendors ID with Goods_ID
3. Crated a seperate table summirizing only Goods_ID and Vendors_ID with no blank results from the table "Operations Details"
4. Created a calculated column in "Goods" table with LookupValue relating Goods_Id with Vendor_ID from the indipendent table from the step 3
as a result I've got the desired connection/relation.. but.. hmmm.. it's so unproffesional! 😞

Please advise more advance solution

Re: Link supplier to corresponding products but in terms of sales quantities

v-robertq-msft — Fri, 03 Dec 2021 07:37:53 GMT

Hi,

According to your description, I can roughly understand your problem, I think you can try to change the data filter direction between table ‘Clients/Suppliers’ and table ‘Operations’ and the filter direction between table ‘Goods’ and table ‘Operations’ from “Single” to “Both” to check if the relationships can work.

For more info, please refer to these two links:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-bidirectional-filtering

And you can get what you want.

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Link supplier to corresponding products but in terms of sales quantities

George1973 — Tue, 07 Dec 2021 08:44:07 GMT

Hi,
First of all, thanks for your respond.

The thing is that, if I change the relationship structure, I will terminate main options, measures and etc.. of the Data Set I have - It's integrated to the existng ERP system and that's why - I can not change the existing relationships.

So, As it seems to me the only solution so far is the one I have wrote here couple of days ago 😞