I've been trying a couple of different approaches to bring together three different data sources into one dashboard, but I've only been successful with getting two out of three sources in correctly. I've attached a workbook so you can take a look. I think the answer is going to involve fixing a calculated field...scroll down for details, or read on for more background.
Here's a summary of what I'm trying to do:
I'm using custom SQL to bring in performance data from SQL server. Unfortunately, that data source lacks a critical crosswalk, which is located on a different server (the table is pretty small, and I can also just dump it into excel, as I did for the attached workbook--either way it's a separate source). I'm also linking to target data in excel.
In my first few attempts, I discovered that it was easiest to bring the target data in using data blending (when I tried to use a cross-database join, the data kept blowing up and giving me incorrect totals). The file I've attached here shows the performance data (distinct count of participants) correctly charted against targets using the blend approach, but with the crosswalk not yet incorporated.
The problem is that I also need to use the crosswalk to reassign a substantial percentage of the participants to the correct geographic area.
First, I tried to bring the crosswalk in as a cross-database join. In some ways that worked--I was able to write a calculated field to pull the correct area for each seeker--but it caused the charts to break. Suddenly, my distinct count of participants, charted against the targets, was no longer working. The error message indicated that the problem was with using a distinct count and a blend at the same time. It seems odd to me that the problem only appeared once I added the cross-database join, since the blend was there all along. Also, the cross-database join did not use the field involved in the distinct count, and the blend also doesn't link on that field.
Next, I got rid of the cross-database join and tried to bring in the crosswalk as another data blend. However, now the calculated field that associates the participants to the correct area is not working.
This formula worked when I was using the cross-database join, but doesn't work with the blended data (with the full datasource.page.field name used for [WDA_Crosswalk]:
IF [WDA_ETOData] = "Central Office" THEN [WDA_Crosswalk]
However, with the data blend (linked on the same field, SeekerZIP), I get the error "All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources". The source fields in the calculated field are text, and in the attached file, that field is called "Final WDA". (It's also stuck in the Measures category when it should be a Dimension).
HelpMe.twbx 1.4 MB