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 expe...
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.
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.
- Thanks a ton Riny_van_Eekelen This really helps. But if you don't mind and accept my apologies, I have no idea why Data Model prompted me to Auto-create a model and then it went ahead and created a new relationship table1, It kept saying, It cannot see any relationship when I have created the relationship. Any ideas, when PQ fails to identify the Relationship already created?
Also I learned about not using the fact table in the filters. Thank you, I could recreate the solution and probably ready to work on data to see if I can make more meaningful analysis,