Power Pivot Relationship Not working causing data not to filter

[DMfba]

I have what I believe is a relationship error in Power Pivot that I can’t seem to figure out. I am attempting to combine multiple excel files and access tables using a unique list of customer vendor numbers. The goal is select the customer vendor number and have other Pivot tables filter based on that selection.

I loaded the various tables into power query, and made sure all of the formatting is correct. Next, I open Power Pivot and set the relationships from my Main Vendor list (unique IDs) over to the different tables. The plan is to use a slicer to filter the various pivot tables. However, there is 1 out of the 5 pivot tables that will not work properly. Basically, this table will not filter. The relationship in power query is connected to this table.

Does anyone have any suggestions on to check for? I created all of the different tables in the same manner and connected them in the same way. If I add the vendor # field to this last table, I can see the filter change as I change the slicer, but the data does not. It will only display the entire data table.

Thank You,

 

[sandy666]

Did you add relevant tables from Power Query to the Data Model?

 

[DMfba]

Hi Sandy,

Thank You for the reply. Yes all of the tables are loaded to the data model. The relationships were created in the design view of power pivot. The main Vendor table is connected to all of the tables and is showing the one to many relationship from the Vendor ( 1 side) to the all other tables (many side).

I am just not sure what is causing the last table to not work.

 

[sandy666]

all what I can do without knowing everything is

Tutorial: Extend Data Model relationships using Excel, Power Pivot, and DAX - Microsoft Support

In this tutorial, you use Power Pivot to extend the Data Model, create hierarchies, and build calculated fields from existing data to create new relationships between tables.

support.microsoft.com

 

[DMfba]

Thank You Sandy. I will review and try to dig a bit deeper and see if I can give you more about what is going on. In short, I can say I added the vendor number or each table the has transactions in the data model and linked to the a Master vendor table.

Another quick thought, the table that is not working is a excel file. Maybe there is just something different with that file compared to the other excel files.

 

[sandy666]

check if the data in this (not working) table have proper format , eg. number as text or dates as text
anyway if you post representative example of source data you'll need to wait for someone else who is better than me in DAX (data model)

 

[DMfba]

Thanks again Sandy. All of the values that I am connecting to are whole number. I also attempted this in a separate workbook using just the master vendor list and the one table that is not working and it still does not work. It will only filter if I use a slicer or field from the original not working table. If needed I can try taking a screen shot later and post them to see if that helps explain thing.

 

[sandy666]

better to use is XL2BB not a picture
or share excel file via googledrive, onedrive or any similar

 

[sandy666]

maybe you need to create bridge table between "not working" table and any table you want to use (just idea only)

Bridge Tables

Dimensional modeling (DM) places and emphasis on the end user's experience. The goal is to create a data model that performs well and is simple to

www.leapfrogbi.com

 

[DMfba]

Hi Sandy or anyone that can shed some light on this,

I have not solved the problem yet but I think I am getting a bit closer. So I tried rebuilding all the tables just to see if I add something incorrectly. Not remembering how I started the pivot tables, I started randomly (in the Power Pivot - Manage section) on one of the data models in To my surprise, the tables that worked before now, no longer work in the new build. I now have the question of why did it work the 1st time but not on the rebuild? My guess here is, how or where I build the Pivot table and slicer maters.

The 1st time I built this I remember opening up Power Pivot, creating the relationships, switching back to data view, then clicking on the Pivot table. As reminder, I have one Master Vendor table (Unique IDs) that link (relationships 1 - many) to the other tables. I believe, I clicked on each data model table then hit the Pivot Table button. I also created a Slicer of the Main Vendor Number from the Master Vendor number table, in order to filter all of the tables.

My big question here is, Does how I create the Pivot table matter? If I made one pivot table and then just copied and pasted it, would that work better? Does making independent Pivot tables from each data table cause a problem? My guess its how I created the Pivot tables last time that made it work, but most likely created the Pivot table for the final table differently that caused the problem.

What is the recommend order to do this correctly. I always thought, as long as the relationships are built it doesn't matter. Simply grab the field you want from all active tables.

Thank You,

David