Scenario:
There are category which are as below
1. Category1 – All the Category2, Category3, Category4 are grouped under the Category1.
2. Category2– Category2 is group of Category3 based on location
3. Category3– Category3 is combination of the multiple Category4.
4. Category4 – Category4 is basically containing similar client project.
There is People Table which contains all the data of the Employee.
Every employee is mapped Category4, in-turn Category4 is mapped to the Category3, Category2
There are other tables which directly has the data for the all Category3, Category4 details where I am using the master which contains all the Category3 and Category4 details. Only problem is in the People Tab. The filter from the Home tab should filter the data into the People tab, hence need to use same filter globally.
Below is the table structure for the Employee table.
-----------------------------------------------------------------------
Emp ID Emp Name Category4 Category4 ID Category3 ID
-----------------------------------------------------------------------
1 A India AZ 001 00023
2 B India PQ 0034 009
3 C India AZ Power 001 00023
Also there is a master table which contains all the space details:
-----------------------------------------------------------------------------------------
Category4 ID Category4 Category3 Category3 ID Category1 Location
------------------------------------------------------------------------------------------
1 India AZ All India AZ 00023 All India Category1 Pune
2 I ndia PQ All India PQ 009 All India Category1 Delhi
3 India PQ All India PQ 009 All India Category1 Pune
Requirement for the Category3 Report:
The requirement is I need a dropdown with all the Category4 and Category3 into the Category4
So my dropdown will contain All India AZ, All India PQ, India PQ, India AZ
When I click on the All India AZ the above data should give me the result of Employee A and C
When I click on India AZ I should get record of A and so on.
Requirement for the Category1 Report:
In the Category1 Report I have dropdown which contains All India Category1, All India Category1-Delhi, All India Category1- Pune.
When I click on the All India Category1 Report this should show all the employees from the Category1 India currently they don’t have other country location which they are planning to make this report go globally.
When I click on the All the employees belonging to the Pune location will be seen and same is for the delhi region
Solution Tried so far:
1. Created ragged hierarchy using the Path DAX function and used the Hierarchy slicer, this give the result but there are some limitations the filter cannot be shared accros the pages(Syncing of the filter with the other pages), the Hierarchy filter is only available in the list form and not in the dropdown where as these people require the same into the dropdown.
2. Created groups in power bi which include all the space under it but it only gives the name of Super Space and not the space names in the Power BI.I tried duplicating the entire set of Space Records so that I can keep that as separate group, but this give problem in creating the relation ship as we need unique records for creating relationship.
