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
Backpacker4Life
Frequent Visitor

Trying to use a SELECTEDVALUE in CONTAINSROW

For context, I'm still fairly new to DAX. In a table visual, I have a list of dates and I am trying to see if those dates match any dates in a holiday dates table. And I want to do so based on the country selected in a slicer. I was able to filter the holiday dates table down to the selected country and then based on the date on the table visual, it shows "same" if the date is on the Holiday dates table and "diff" if it isn't on the holiday dates table:

 

VAR countryHoliday = DISTINCT(SELECTCOLUMNS(FILTER( 'zHolidays','zHolidays'[Country] = SELECTEDVALUE( 'zLocation'[Country] ) ),"Dates", [List of Dates]))

RETURN
IF(CONTAINSROW({SELECTEDVALUE( zDate[Date] )}, countryHoliday), "same", "diff")
 
This works as expected when this measure gets added to the table visual which has the zDate[Date] column.
 
I then want to be able to see if 2 days prior to each date in the zDate[Date] column is on the holiday dates calendar. So I tried this:
IF(CONTAINSROW({SELECTEDVALUE( zDate[Date] )-2}, countryHoliday), "same", "diff")
 
But this doesn't work and I can't understand why. And I can't use DATEADD([zDate[Date], 2, DAY) because that doesn't seem to work either.
 
Any ideas? 
1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

Delete this relationship:

Vlianlmsft_0-1636016542296.png

In addition, you can abbreviate the formula to this:

Working Measure = 
 
IF(SELECTEDVALUE( zDates[Date])in VALUES('zHoliday Calendar'[List of Dates]), "holiday", "regular")

Vlianlmsft_1-1636016656878.png

 

View solution in original post

8 REPLIES 8
V-lianl-msft
Community Support
Community Support

Delete this relationship:

Vlianlmsft_0-1636016542296.png

In addition, you can abbreviate the formula to this:

Working Measure = 
 
IF(SELECTEDVALUE( zDates[Date])in VALUES('zHoliday Calendar'[List of Dates]), "holiday", "regular")

Vlianlmsft_1-1636016656878.png

 

Thanks! The shorter formula worked for the working measure. What did you do for the not working measure? I'm not sure how to get it to look at 2 days prior.

Please refer:

Not Working Measure = 
 
IF(SELECTEDVALUE( zDates[Date])-2 in VALUES('zHoliday Calendar'[List of Dates]), "holiday", "regular")

Wonderful, thank you for your help!

PhilipTreacy
Super User
Super User

Hi @Backpacker4Life 

Thanks but I'd still need to recreate your data and tables etc.  You can use OneDrive, Dropbox or Google Drive to upload your PBIX and then link to it.

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi @PhilipTreacy,

 

Thanks for the suggestions. That was helpful. The file of the mock up of the data can be found here: https://drive.google.com/file/d/1IVz5bwacT6d8Tl_LABeGxeIu4ffVuzOp/view?usp=sharing

 

Kind regards,

Rebekah

PhilipTreacy
Super User
Super User

Hi @Backpacker4Life 

Can you please supply your PBIX file so I can see your data.

regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Hi @PhilipTreacy , I couldn't figure out how to attached a PBIX file, but here are some screen shots of the table visual and the model.

2021-10-29_17-28-43.png2021-10-29_17-29-25.pngPBIX file.

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.