How to combine two slicers to select data for visualization

[ShaneK]

Hi,

I'm experiencing a problem when trying to use two slicers to select data for my visualization - here's a summary:

Problem Summary
Objective: I want to create a measure in Power BI that sums the hours for staff members based on two conditions:
• The hours for the selected Team, regardless of the Delivery Portfolio, plus

• The hours for the Team = "Team Two" where the Delivery Portfolio matches the selected value.

Current Setup:
• I have a matrix visualization that shows 'Export'[Staff Name] in the Rows, Weeks in the Columns, and the sum of 'Export'[Hours] in Values.
• I have a slicer to select the Team ('Export'[Team]).
• I also have a slicer to select the Delivery Portfolio ('Export'[Delivery Portfolio]).

Challenges:
• Ensuring that the measure correctly sums the hours for the selected Team without being affected by the Delivery Portfolio.

• Including the hours for "Team Two" where the Delivery Portfolio matches the selected value, without duplicating totals or missing data.

Can anyone please advise how I should approach this?

Thanks!

[tharunkumarRTK]

@ShaneK 

I hope this below expression solves your requirements 

Var __SelectedTeam = Selectedvalue(Export[Team])
Var __SelectedDeliveryPosition = Selectedvalue(Export[Delivery Postion])
Var __measure1 = calculate(sum(Export[Hours]), REMOVEFILTERS(Export[Delivery Postion]))
Var __measure2 = Calculate(sum(Export[Hours]), Export[Team] = "Team Two")
Return __measure1 + __measure2

For further assitance, please share the sample data and expected output

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[ShaneK]

Hi and thanks for your response.   I’m not familiar with the use of variables, but I have translated your code to the following:

CombinedHours =

VAR __SelectedTeam = SELECTEDVALUE('Export'[Team])

VAR __SelectedDeliveryPortfolio = SELECTEDVALUE('Export'[Delivery Portfolio])

VAR __measure1 = CALCULATE(SUM('Export'[Hours]), REMOVEFILTERS('Export'[Delivery Portfolio]))

VAR __measure2 = CALCULATE(SUM('Export'[Hours]), 'Export'[Team] = "Team Two" && 'Export'[Delivery Portfolio] = __SelectedDeliveryPortfolio)

RETURN __measure1 + __measure2

What I’m trying to achieve is:

1. to be able to filter on Team, using the Team slicer, to allow my matrix to display all forecast hours, by week, for all the team resources within the selected Team.
2. additionally, I want to also display all forecast hours, by week, for all the generic resources (i.e. not members of the Team selected above) which are forecast within the Delivery Portfolio selected by the Delivery Portfolio slicer.

Can you please advise how to amend the DAX formula above to achieve this?  And further advice would be greatly appreciated.

Thanks!