Forum Discussion
Can I use power query to analyse multi tables to create multi filters and slicer?
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.
- 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,
