Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danielco
Regular Visitor

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 7
IanSwanepoel
Helper I
Helper I

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

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

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.Capture2.PNGCapture3.PNG.
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.

Capture4.PNG
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.
Capture5.PNG
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.

v-chuncz-msft
Community Support
Community Support

@danielco,

 

According to your description, you may build relationship on [campaign] column.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.