cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kthakk27
Helper I
Helper I

Measure Change Based on Slicer Selections

I have 3 slicers coming from a date table:

Fiscal Year (Includes 2020, 2021, 2022 etc)

Month Name (Includes May, June, July etc)

Period (Includes Period 1 and Period 2)

 

I have the following measures:

Total Hours for Period

Total Hours for Month

 

I'm looking to have a new single measure based on slicer selections.

1. For example, if FY21 and Month Name: February is selected I would like to return the Total Hours for Month

2. Example 2, if FY21, Month Name: February, Period 1 or 2 is selected then I would like to return Total Hours for Period

3. Example 3, if FY21 is selected then I would like to return the Total Hours for Month (over 12 months)

I was looking online and it looks like I can use Hasonevalue and Selectedvalue, but wasn't sure how that would work exactly. I started with this:

IF(HASONEVALUE('dim Date'[Period]),
IF(SELECTEDVALUE('dim Date'[Period]) = "Period 1",
[Standard Hours (new) for Each Period],
IF(SELECTEDVALUE('dim Date'[Period]) = "Period 2",
[Standard Hours (new) for Each Period]
)))
 
If I selected a FY and Month the measure above returns blank. If I selected Period 1 or Period 2 it returns the correct values. The problem is when I unselect from the Period slicer I want it to return the month value then or if month value is unselected to return the total for the FY.
1 REPLY 1
RicoZhou
Community Support
Community Support

Hi @kthakk27 

Does your Hour table contain a date column?

From your statement, you just want to filter the sum of hours by Year, Month and Period in Dim Date Table.

So, I think you don't need to build a measure. Just build a relationship between Date columns in two tables.

For reference : Create and manage relationships in Power BI Desktop

Then the result will be filtered by slicers.

Or you can share a sample without sensitive data with me by your Onedrive for Business.

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.