Measure Change Based on Slicer Selections

[Anonymous]

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.

[v-rzhou-msft]

Hi @Anonymous 

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.