Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
HassanAshas
Helper V
Helper V

How to make a slicer based upon a measure that calculates Aggregate value, using DAX

Hi, I have a dataset like this, 

 

 

Emp CodeNameAllocationProject Code
1Matt0.4Bench
1Matt0.2A
1Matt0.3C
2Avery0.6A
3Aryub1C
4Jacob0.1Bench
4Jacob0.1C
4Jacob0.4A
5Zara0.6Bench
5Zara0.4C

 

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

 

HassanAshas_0-1677078618142.png

 

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. 

 

HassanAshas_2-1677078705743.png

And similarly, in Feb, it should show, 

 

HassanAshas_3-1677078728897.png

 

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

 

1 REPLY 1
HassanAshas
Helper V
Helper V

Sorry for bumping, but anyone who can help on this? Thanks. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.