Relationships between groups

[Anonymous]

Hey, I have a data set looking like screenshot following, but I have met a wall, and I know this is simple cut cannot conceptualise this:

In my set, an order can come from several sources: adwords, mail, blogger, call centre (cc). It can also have several sources (mainly adwords and blogger, or adwords and cc).

How can I create a relationship between a table I created (dim_team, on the left) and fact_order on the right, so that when I select a team, it will filter only orders made by that team (or shared with other teams).

Also, which visualisation do you recommend to show how mnay orders are shared between teams? I was hoping to use something like a Venn, rather than creating new conditional columns with permutations of sources.

[Anonymous]

Hi, @Anonymous  

Thanks for parry2k's concern about this issue.

parry2k's method is correct.

Based on the dataset you provided, first you should hold Ctrl and do a multi-select of the OrderID and PK_Date columns in PowerQuery Editor:

Then select Unpivoted other columns:

And the result should look like this, not like your result:

And then close&reply.

Create a relationship between this table and your table dim_team:

You can then add a Slicer for data filtering:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

 how about this part? 

Now set the relationship from the dim table to the attribute column in the fact table and that's it. Now you should be able to slicer the fact table from the dim table

@Anonymous

[parry2k · Accepted solution]

@Anonymous so you should unpivot the data by following these steps:

Transform Data -> Select Order id and pK_Date columns -> Right click -> Unpivot other columns

It will add two columns, attribute and value, rename as you want it, close, and apply.

Now set the relationship from the dim table to the attribute column in the fact table and that's it. Now you should be able to slicer the fact table from the dim table

[Anonymous]

hello @parry2k  i think i managed to unpivot the tables without duplication of the orders, but how do I now use it so that I can select a channel and the table will filter based on which channel I want? channels are in different columns:

[Anonymous]

Thanks @parry2k , i tried that and this would create duplicate entries for orders which have more than channel assigned to them. Is there a way to avoid that?

[parry2k]

@Anonymous not sure what is the shape of your fact tables. Can you share? It should be unpivoted if it is not already.

[Anonymous]

Hey @parry2k , it looks just like that, plus some columns. the flags from_adwords, from_blogger etc are a result of SQL modifications at the source (in power query I put a sql script)

OrderID	PK_Date	from_adwords	from_blogger	from_cc	from_mail	from_organic
11899222	02/06/2024 00:00	1	0	0	1	0
11899243	02/06/2024 00:00	1	0	1	1	0
11899245	02/06/2024 00:00	1	0	0	1	0
11899777	02/06/2024 00:00	1	0	0	1	0
11899806	02/06/2024 00:00	1	0	0	1	0
11899873	02/06/2024 00:00	1	0	0	1	0
11899878	02/06/2024 00:00	1	0	0	1	0
11899884	02/06/2024 00:00	1	0	0	0	0
11899898	02/06/2024 00:00	1	0	0	1	0
11899903	02/06/2024 00:00	1	0	0	1	0
11899956	02/06/2024 00:00	1	0	0	1	0
11899989	02/06/2024 00:00	1	0	0	1	0
11899995	02/06/2024 00:00	0	0	0	1	0
11900036	02/06/2024 00:00	1	0	0	1	0
11900072	02/06/2024 00:00	1	0	0	1	0