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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-rzhou-msft
Community Support
Community Support

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. 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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