Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hi guys,
After a few hours of research i need to ask this as i can't seem to find it
i have two excels
first with a campaign name website and some target indicators
second has many campaigns, many websites many results
I managed to combine the 2 files in order to get a before and after view. (all websites with all results)
Now i want to make a filter/slicer to see only the data for the campaign field from the first excel.
the problem is that i get the right websites, but with wrong indicators ( instead of showing the indicators only from filtered campaign, i get it from all campaigns).
So if website A in campaign X has a value of 1 and the same website has value 3 in campaign Y. When i filter for campaign X, the A website will have a value of 4 (campaign X+campaign Y), instead of showing only value 1.
Thanks
You need to apply a filter based on something that would be unique to that table, something that would not be found in the other table.
If you still haven't managed do you mind sharing your .pbix and/or your relationship view?
Hi Ian,
I've made a replica of my files
Please see below the .pbx
Most likely is something that i am missing
Or my approach is wrong
In the above file. when filtering for FAR FIR campaign, Ringier RO should have 400 impressions
thanks
Hi Daniel,
Had a look. See that the campaigns across the 2 tables are different, even for the same sites.
Eg in 6539_Untitled aerotravel.ro is connected to both 201612 ASD DFA as well as 201611 FAR FIR, whereas in sheet 1 aerotravel.ro is only connected to 201611 FAR FIR.
The filter you applied appears to be from 6539_Untitled so it pulls through the value based on the affected websites(for relationship). So filtering on FAR FIR will pull through ALL the values connected to aerotravel.ro, which includes the values for ASD DFA, because there is no direct relationship between the Campaign field..
This results in the impressions including values for the other campaigns. However when filtering on Campaign directly from Sheet 1 you get the following: Which has the 400 as specified in your post.
Hope this helps.
P.S. In regards to the fact that a relationship cannot be established because there are multiple values in both tables. You can make a bridging table. This is how I generally solve the issue of many to many relationships.
As displayed above I created a custom table which contains only the unique values for Campaign (now there are no duplicates)
So you can connect it to both tables and create an indirect relationship between the 2. (This is also popularly refered to as a FACT table)
thanks a lot for your time
I'll take a look
Just to give you some background:
the 6539_Untitled file usually contains a few thousand lines. With many campaigns, each website can appear in as many campaigns as possible. and i have 2 more files like this.
I want to use Sheet 1 table to extract all data related to 1 campaign in all tables available.
I need to be able to change what is in Sheet 1 (campaign name) at any point and therefore get reports for as many campaigns as needed
Thanks again
Then my reccomendation would be to concatinate the Website and Campaign field to create a key, seeing as it is based on both.
Then using that value to establish the relationship.
According to your description, you may build relationship on [campaign] column.
thanks for your answer, but i can't make relation on campaign as it says that one column must have unique values
Most likely i'm missing something really basic
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |