Forum Discussion

danielco's avatar
danielco
Regular Visitor
8 years ago

Filter like in Pivot

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

7 Replies

    • danielco's avatar
      danielco
      Regular Visitor
      8 years ago

      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

  • IanSwanepoel's avatar
    IanSwanepoel
    Helper I
    8 years ago

    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?

    • danielco's avatar
      danielco
      Regular Visitor
      8 years ago

      Hi Ian,

       

      I've made a replica of my files 

      Please see below the .pbx

      https://we.tl/LRNVancOtx

       

      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

      • IanSwanepoel's avatar
        IanSwanepoel
        Helper I
        8 years ago

        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)