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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
curtismob
Helper IV
Helper IV

Slicer Selected Value Not Working in Calculated Column Logic

Hello,

I am having what I think is a context issue.  I am basically trying to use the year from a datetime dimension table in a slicer and use the selected year to filter data in another table.  I created the following measure to capture the year selected:

 

Year Selected =

IF (

ISFILTERED ( '_Date Time Slicer'[Year] ),

IF( HASONEVALUE (  '_Date Time Slicer'[Year] ), LASTNONBLANK( '_Date Time Slicer'[Year], 0), YEAR(NOW())),

YEAR(NOW())

)

 

I have used cards to verify the above logic is working when selecting different years.  Since it wasn’t working initially, I created a measure in the ‘Directs’ table referencing the measure defined above as follows:

 

_Year Selected = '_Date Time Slicer'[Year Selected]

 

I know measures aren’t necessarily specific to a table, but I thought I would try it anyway.

 

Finally, the goal is to use the year selected from the slicer to drive the date range logic below, which is a calculated column.

 

Jan DCC Budget =

IF(Directs[Effective].[Date] <= DATE(Directs[_Year Selected], 1, 5) &&

   Directs[Expiration].[Date] >= DATE(Directs[_Year Selected], 1, 5), Directs[Cost], 0)

 

I am currently only using previous and current years 2017 and 2018.  For the above example, the Directs[Cost] sum does not change, it is always the amount for 2018, even when selecting 2017.

 

Any help/suggestions would be greatly appreciated.

 

Thank you,

@curtismob

1 ACCEPTED SOLUTION

This following appears to be working:

 

Jan DCC Budget as Measure =
CALCULATE(SUM(Directs[Cost]),
    FILTER(Directs, Directs[Effective] <= DATE(Directs[_Year Selected], 1, 5) && Directs[Expiration] >= DATE(Directs[_Year Selected], 1, 5)))

View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

The following should be measure, you should use aggregation with cost

 

Jan DCC Budget as Measure =
IF(Directs[Effective].[Date] <= DATE(Directs[_Year Selected], 1, 5) &&
   Directs[Expiration].[Date] >= DATE(Directs[_Year Selected], 1, 5), SUM(Directs[Cost]), 0)


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k, thank you for the quick response, but unfortunately your suggestion doesn't work. 

 

Directs[Effective].[Date] and Directs[Expiration].[Date] are Directs columns, not measures, so they are not valid for use in a measure.  These are both red underlined.

 

I will try and see if using CALCULATE will work.

I tried the following:

 

Jan DCC Budget as Measure =
CALCULATE(SUM(Directs[Cost]), Directs[Effective] <= DATE(Directs[_Year Selected], 1, 5), Directs[Expiration] >= DATE(Directs[_Year Selected], 1, 5))

 

The above with the follwoing error.

 

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

This following appears to be working:

 

Jan DCC Budget as Measure =
CALCULATE(SUM(Directs[Cost]),
    FILTER(Directs, Directs[Effective] <= DATE(Directs[_Year Selected], 1, 5) && Directs[Expiration] >= DATE(Directs[_Year Selected], 1, 5)))

Sweet, good for you. There you go!!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Sorry I missed that, yes please sure calculate



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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