Forum Discussion
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)
Selectedvalue = 2013
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.
2 Replies
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.