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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

How to use selectedvalue and column values.

Hello everyone. I have a dataset looking like this:

 

Date ordered:

Date:
1-1-2012
5-6-2013
6-8-2014

This tabel has lots of dates. I use the YEAR value of this table to select a year. 

 

Next i have a table with:

NumberNameActive fromActive till
111Name11-1-201231-12-2100
222Name21-1-201231-12-2012
333Name31-1-201231-12-2013
4556Name41-1-201431-12-2100
5775Name51-1-201431-12-2100
6574Name61-1-201431-12-2100
87544name71-1-201431-12-2100

 

So now i want to create a field, showing wich number was active at the selected date. 

Example i select 2013. this should show up.

 

NumberNameActive fromActive tillWasActive
111Name11-1-201231-12-21001
222Name21-1-201231-12-20120
333Name31-1-201231-12-20131
4556Name41-1-201431-12-21001
5775Name51-1-201431-12-21001
6574Name61-1-201431-12-21001
87544name71-1-201431-12-21001

 

I tried doing this with a measure, but i cannot use columns in a measure. The i tried it using a calculated column, but i can not use selectedvalue in a calculated column. 

The calculated column looks like this:

WasActive = IF(AND([Selectedvalue] >= Table2[Active from].[Year];[Selectedvalue] <= table2[Active till].[Year]);1;0) 

 

Where i now have the simple measure:
Selectedvalue = 2013 

 

I used to have the selected value take the value from the slicer, but that ofcourse does not comply with the calculated column.
 
Anyone knows how i can fix this?
 
Thankyou.
1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @EricHulshof,

 

If I understand you correctly, SELECTVALUE() function might be helpful for you.

Please check following measure and see if the result achieve your expectation:

Measure =

var sv = RIGHT(SELECTEDVALUE('Date'[Date]),4)

return

IF(RIGHT(MAX('Table'[Active from]),4)<=sv&&RIGHT(MAX('Table'[Active till]),4)>=sv,1,0)

The Date column in my sample data is Text type, you can use FORMAT() function to change type if it is Date type in your data.

Result would be shown as below:

1.PNG

BTW, Pbix as attached, hopefully works for you.

 

Best Regards,

Jay

 

Community Support Team _ Jay Wang

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

2 REPLIES 2
v-jayw-msft
Community Support
Community Support

Hi @EricHulshof,

 

If I understand you correctly, SELECTVALUE() function might be helpful for you.

Please check following measure and see if the result achieve your expectation:

Measure =

var sv = RIGHT(SELECTEDVALUE('Date'[Date]),4)

return

IF(RIGHT(MAX('Table'[Active from]),4)<=sv&&RIGHT(MAX('Table'[Active till]),4)>=sv,1,0)

The Date column in my sample data is Text type, you can use FORMAT() function to change type if it is Date type in your data.

Result would be shown as below:

1.PNG

BTW, Pbix as attached, hopefully works for you.

 

Best Regards,

Jay

 

Community Support Team _ Jay Wang

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

Allright!

 

i knew about the selectedvalue function, i just couldnt figure out how to properly use the column values. Max and Right where the things i was looking for!

 

Thanks alot!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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