Forum Discussion
Can I use power query to analyse multi tables to create multi filters and slicer?
https://ufile.io/slw11gp8
Please find the example attached. It's a basic query, the primary key is the unique names and regions linked to the actual query.
When I Insert the Pivot table, I was expecting I could filter based on names and I get the data. But my excel filter works way better. Please may I know why I am not able to do the basic analysis?
Note: I do not want to change the original table, I just created references and created unique tables of names and regions and created a data model to create relationships and then I use this as a filter to get the display. I could as well do a multi-level filter but I want to take advantage of the power query.
Note: I retained the names and regions as primary keys. whcih are unique
4 Replies
Mythbuster Your example is indeed very basic. All data is already well structured in one table. There is no need to create multiple tables from it with PQ and put it in a DM.
The Pivot table you are trying to make has the Employer from the Employer (dimension) table in the filter field, Employer from the Profit Analysis (fact) table in the column field and the Profit (amount) from the Profit Analysis (fact) table in the row field. That not going to deliver meaningful results, I believe. What is it that you would want to show in the pivot table?
- Riny_van_Eekelen
Thank you very much.
Yes I have intentionally using a simple relatable example to ensure power pivot can be related to table with filters. Once I get understanding and able to create data relations correctly I want to use this to office example to anlayze data better,
This is what I intend to do:
Create queries and relationships and
1. When I filter for Employee,
I need to get regions the employee associated, Profit and Sum of total profit for that employee in single row (differnt columns)
Likewise
2. I can filter for region and it should give
Employers list, Profit
Please may I know what mistake, I am doing? Also
When I have already created relationships, when I insert pivot, it was prompting me that there are no relationships so I have to click Auto-create relationships.
Your help is truly appreicated.Mythbuster It goes beyond the scope of this forum to explain in depth how Power Pivot / DAX works. You'll find plenty of resources on-line which explain from the basics up to the most advanced level, much better than I can.
With regard to your simplified example, I changed the DM, added a simple measure for Total Profit and I have hidden certain fields to prevent that one accidentally drags a field from a fact table to a row/column. You'll find a pivot table beside the original data table.
Have a look at the attached file. I hope it get's you started learning about Power Pivot and DAX.
