Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |