Can you post a small anonymous TWBX so we can see can better understand your issue?
I can think of some possible solutions:
- Can you join your data, rather than blending it, to ensure every value is associated?
- For those worksheets where you don't need the linked field: check to make sure the table containing the data you require is the primary file (in blue, rather than orange).
Perhaps help us understand why you need to unclick links? In order for proper blending, you need at least one linking field. If you cut that link, you'll see things start to unravel-- just as you've observed.
Please see the attached file.
I am trying to look at the number of volunteer (coach) placements compared to goals for the last 2 years.
It would appear that I can't join the data because I am using a Salesforce extract for which cross-database joins are not supported.
I want to be able to filter this chart based on the "Market Designation" field. I have named it different things in each data source ("Market Designation (CP)" and "Market Designation (G)") but have created the blending relationship. The Goals sheet is the primary data source.
When I click to activate the link between Market Designation (CP) and Market Designation (G) is when the values in the "Weekly Coaching Goal" column shrinks.
I am happy to answer any further questions that would help you help me! I greatly appreciate the time and support.
Data Blending Issue example.twbx 728.7 KB
I received an error, say "Volunteered Started Coaching date" was not in the database?
Did you get the same issue? --Michael
The Volunteer Started Coaching Date in the GOALS data source (spreadsheet) I don't believe should actually be relevant. I had originally put that in there to try to line things up with the dates on the Coach Placements but instead found a way to do that with the Week field and then a string field I calculated onto the Coach Placements data source.
There should, however, still be a Volunteer Started Coaching Date field on the Coach Placements Salesforce extract data source. This is the field that we want to use to group the records by week to count.
Whew! Well... I may have come up with a solution.
After quite a bit of brain-work, I scratched the blending option: I couldn't quite explain why things weren't matching up.
I DID see you had some values in one list that weren't present in the other, but I don't think that was the issue.
I settled on making a parameter for [Market] (adding an All choice)...
Next I added filters for both [Market Desigination (G)] and [Market Desigination (CP)]...
The logic in each of the markets read something like:
IIF([Market]='All',1=1,[Market Designation (G)] = [Market])
I think this may generate the results you were after.
I still can't explain why the blend was so wonky...
The user doesn't have the functionality of choosing multiple markets-- just one or all
Please let me know what you think. If you'd like me to upload a copy, I can (but as you can see, my changes were pretty simple)
Thanks for all of your brain work, Michael!
I am not sure if the parameter solution is going to work for exactly the reason you pointed out in your second Con. I had thought about this but I suspect that we are going to want to be able to select multiple regions at a time (because staff oversee multiple regions) so I reverted back to the quick filters. Although another thought I had was if it would be possible to use a grouping to somehow create the bunches that I know would be used most commonly, however, I want to avoid going down the trail that might end up with creating every possible permutation of combinations. Would there be a way to avoid this?
Back to the original lists, can you point out to me which options are not consistent across all of them?
Thanks so much!
Market designation (G) includes:
- Market Designation
Market Designation (CP) includes:
Perhaps a good step would be to see which values (blended or unblended) are correct. Are you able to figure this out?
You can view the data (export it if you need to). Perhaps the blended is correct? If so-- you're set! I'm guessing they ARE correct, so you can use a simple filter regular filter for it.
Why are values disappearing? Maybe they are from past seasons?
Maybe it's due to clipped weeks. It seems you have a link to Week Start Date, which is calculated on a date (like Aug 21), but your weeks are strings (Week 35, etc.). Week 35 in 2016 won't represent the same time period as 2015 because 2016 is a leap year. Seem far fetched?
Anyway, I'd dig into your data (blended and non) and see which results are correct. You can decide where to go from there!
I will definitely look into all of those things and see if I can figure out a solution!