Trouble with relationship and/or slicer

[tmyers1152]

I have run into a problem with a sales report I am doing. I have sales to one customer that needs to also be assigned to another customer but still show up for both, so if you have customer 1 and 2, when you filter by 1 you see of their sales but when you filter 2 you see all of their sales plus some of customer 1. My facts table (tblTicketDetails) and diminsion table (tblCustomers) are linked via a 1 to many relantionship on account #. I made a new field in tblCustomers called LookupAccount (thank you to reddit for the idea) where nearly all the numbers match the account # except where I need them to be assigned to multiples, in which case account # is one thing but lookupaccount is another (such as account 123 has a lookupaccount of 345 rather than also being 123).

How do I apply this practically? I can't have my slicers using lookupaccount as it would be numbers that made no sense to the user so I have to keep it as the customer name. I can't join the table via lookupaccount as that creates a many-to-many which I personally dont understand how to work with (and when I did, I did not notice a change).

Here is a snip of the two tables in question just for reference.

[v-kkf-msft · Accepted solution]

Hi @tmyers1152 ,

Suppose you have the following sample data and expect the output to be that when the slicer selects Account 1, the fact table will filter the values of Account 1 and 2.

   

If this is the case, please create the following new table in Power Query and then create the relationships between the tables.

let
    Source = Table.SelectColumns(tblCustomers,{"Account #","LookupAccount"}),
    AppendAccount = Table.AddColumn( Table.SelectColumns(tblCustomers,"Account #"),"LookupAccount", each [#"Account #"]),
    CombineTable = Table.Combine({Source, AppendAccount})
in
    CombineTable

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.