Linking and Filtering Questions

[Ray42]

I'm trying to do something that would seem to be fairly easy.

I want to report/analyze on a monthly basis how my forecasted and actual costs change each month of the fiscal year.

I have three spreadsheets that are formated exactly the same - budget categories in column a, a category number in column b, months and quarters starting in column c

I've imported each into PowerBI so that I have three tables with 4 columns

• category
• category #
• period
• value

My first issue was that the tables could not be linked since they were all teh same- I think that was the reason.  I solved that by adding a fourth table called "Crosswalk" that included one column of the catgories that I was interested in - a total of eight.

I was able to link the other tables to "Crosswalk" and thought all was fine.

When I build the reports in PowerBI using a slicer for category (from the crosswalk table) I can filter the reports, but the numbers are not accurate for teh other tables.  The situation gets worse when I add a slicer for "period" using the field in any of the other tables.

In short what happens is that the table where the field "period" was used as in teh slicer is accurate but the others are not - they tend to be a summatiin of all the fields.

I thought that the situation might be fixed if I added additional links for Period, but the links are not allowed due to (i believe) the similarity of the data. 

Interested in hearing any thoughts.

[Sean]

Sounds like you need to Append the 3 Queries into 1.

Make sure you uncheck Enable Load for each Query.

Then Append all 3 into a new one and load only that one into the Model.

Look at my response here http://community.powerbi.com/t5/Desktop/Append-query-duplicating-Current-Table/m-p/27367#M8898

You may need to add a Unique identifier column for each table... Before appending

[kcantor]

@Sean is spot on (as usual!). If you have three tables that are close to the same, it makes more sense to turn them into one. Use you bridge table to populate the rows but pull the facts from the big appended table.

[Ray42]

I made some clumsy (new user) attempts to do that, but let metry again.  Thanks for the help.