Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
Hi, I have a dataset like this,
Emp Code | Name | Allocation | Project Code |
1 | Matt | 0.4 | Bench |
1 | Matt | 0.2 | A |
1 | Matt | 0.3 | C |
2 | Avery | 0.6 | A |
3 | Aryub | 1 | C |
4 | Jacob | 0.1 | Bench |
4 | Jacob | 0.1 | C |
4 | Jacob | 0.4 | A |
5 | Zara | 0.6 | Bench |
5 | Zara | 0.4 | C |
I have two values that are being calculated through DAX Measures (special thanks to tamerj1 for the help in creating these)
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.
And similarly, in Feb, it should show,
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
Sorry for bumping, but anyone who can help on this? Thanks.
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
12 | |
11 | |
10 | |
9 |