Creating a slicer/filter based upon a measure that calculates aggregate values

[HassanAshas]

Hi, I have a dataset like this, 

Date	Emp Code	Name	Allocation	Project Code
1/1/2023	1	Matt	0.4	Bench
1/1/2023	1	Matt	0.2	A
1/1/2023	1	Matt	0.3	C
1/1/2023	2	Avery	0.6	A
1/1/2023	3	Aryub	1	C
1/1/2023	4	Jacob	0.1	Bench
1/1/2023	4	Jacob	0.1	C
1/1/2023	4	Jacob	0.4	A
1/1/2023	5	Zara	0.6	Bench
1/1/2023	5	Zara	0.4	C
2/1/2023	1	Matt	0.4	Bench
2/1/2023	1	Matt	0.2	A
2/1/2023	2	Avery	0.6	Bench
2/1/2023	2	Avery	0.3	A
2/1/2023	3	Aryub	1	C
2/1/2023	4	Jacob	0.5	Bench
2/1/2023	4	Jacob	0.4	A
2/1/2023	5	Zara	0.8	Bench
2/1/2023	5	Zara	0.1	C

I have two values that are being calculated through DAX Measures (special thanks to tamerj1 for the help in creating these)

1. Non-Allocated Resources: Total Number of Employees who are more than 50% on Bench 
2. Allocated Resources: Total Number of Employees who are more than 50% on Projects other than Bench '

These are being calculated based upon the following logic, (considering Emp No. 1 Matt in December Month)

For Emp Code: 1, Matt 

Allocation on Projects: 0.2 + 0.3 = 0.5 
Allocation on Bench: 0.4 
Total Allocation = 0.4 + 0.5 = 0.9 

So, to calculcate individual ratio on projects/bench,

Total Allocation on Projects = Total Allocation on Projects / Total Allocation 
Total Allocation on Projects: 0.5 / 0.9 = 0.55

Total Allocation on Bench = Total Allocation on Bench / Total Allocation 
Total Allocation on Bench: 0.4 / 0.9 = 0.44

Since Allocation on Projects > Allocation on Bench, so Emp Code is Allocated

The DAX that is being used to solve this (it's same as provided by tamerj1) 

Total Allocated Employees = 
SUMX (
    VALUES ( Competency[Name] ),
    VAR CurrentEmpTable = CALCULATETABLE ( Competency )
    VAR OtherProjects = FILTER ( CurrentEmpTable, Competency[Project Code] <> "Bench" )
    VAR TotalAllocation = SUMX ( CurrentEmpTable, Competency[Allocation] )
    VAR OtherProjectsAllocation = SUMX ( OtherProjects, Competency[Allocation] )
    VAR PercentAllocation = DIVIDE ( OtherProjectsAllocation, TotalAllocation )
    RETURN
        INT ( PercentAllocation > 0.5 )
)

(and same for Non-Allocated Resources, with a little bit of tweek in the logic)

What I need to do is actually Filter my Table based upon these two measures. That is, I need to provide user with a slicer to select between Allocated and Non-Allocated Resources. 

If he selects Allocated then it should filter the data to only contain those employees who are allocated.

Issue that I am facing is, it is not possible to make a calculated column because for every employee, I need to access multiple rows in the current filter context (e.g. Month selected) and then check if he is allocated or not). And if the filter context changes (e.g. we select all the Months) then the value for calculated column will also change. 

I tried to create a SUMMARIZE Table where I summarized the fact table based upon Emp ID and Date, and stored SUM of the Allocated values for each employee and then tried to filter using that table, but again, that didn't work because it is possible that the filter context might change. 

Result I am trying to achieve:

Right now, it is showing me complete data, like this

What I need to do is if I select Allocated Resources (with Month filter selected to January), it should show me only the following 4 resources (that have allocation = 1) and also change total value accordingly. So, from the following picture, Zara should be excluded. 

And similarly, in Feb, for Allocated Resources, only Aryub should be visible. 

Can anyone help out in resolving such a problem? I did try to look into various solutions such as using Field Parameter, but couldn't find any that helped me out, or maybe I couldn't understand those solutions properly. If you are aware of any good solution, please refer me to that, would be grateful for that. 

If you would like to download the Power BI file, you may do so from here: https://drive.google.com/file/d/1wh33gJkQelff3pC60K1AHELcsonTAQa4/view?usp=sharing

[Anonymous · Accepted solution]

HI @HassanAshas,

You can create a new calculated table with "Non-Allocated Resources" and "Allocated Resources" types and use this new field as source of slicer.

Then you can write a measure formula to extract the selection to compare with current calculation results and return flag.  

After these steps, you can use the formula on the ‘visual level filter’ to interact and filter records based on slicer selections.

Regards,

Xiaoxin Sheng

[HassanAshas]

Yes, exactly! This is what I did and managed to achieve my desired results. 

I made a Calculated Table where I summarized the employees by Date and Emp ID and then made a calculated column in my main Fact Table where I concatenated Date - Emp ID and made a Relation with this new Calculated Table using this Composite Key. 

Now, I was easily able to filter out records using this new Calculated Table. Thank you so much for the response! 
I am not sure if this is the most efficient way of solving this problem however, but it does solve the issue.