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
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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.