cancel
Showing results 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

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:

 Number Name Active from Active till 111 Name1 1-1-2012 31-12-2100 222 Name2 1-1-2012 31-12-2012 333 Name3 1-1-2012 31-12-2013 4556 Name4 1-1-2014 31-12-2100 5775 Name5 1-1-2014 31-12-2100 6574 Name6 1-1-2014 31-12-2100 87544 name7 1-1-2014 31-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.

 Number Name Active from Active till WasActive 111 Name1 1-1-2012 31-12-2100 1 222 Name2 1-1-2012 31-12-2012 0 333 Name3 1-1-2012 31-12-2013 1 4556 Name4 1-1-2014 31-12-2100 1 5775 Name5 1-1-2014 31-12-2100 1 6574 Name6 1-1-2014 31-12-2100 1 87544 name7 1-1-2014 31-12-2100 1

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

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.
2 REPLIES 2
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:

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!

Announcements

#### 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 Monthly Update - June 2024

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

#### 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.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors