Another slicer value between two column values post

[AJTindall73]

Good afternoon.

I have a slicer containing Year values from a stand alone Year table.

I have a data table which, amoung other columns, includes two columns, Start Year and End Year.

I want to use the slicer value to check if it is between the start year and end year of each row in the table.

For example:

CalculatedMeasure = If(selectedvalue(YearSlicer) >= StartYear && selectedvalue(YearSlicer) <= EndYear, 1, 0)

I would like to then display all the rows and columns of the data table where the the CalculatedMeasure = 1

My problem is that a calculated measure doesn't lend itself to row level filtering and this really needs to be a Calculated Column, but the selectedvalue feature only works in Calculated Measures.  

I have searched the Power BI Community posts and not found exactly what I was looking for.

So my quesion is: How can this be done?

[Anonymous · Accepted solution]

Hi @AJTindall73 

You can try to add your measure into the Filter Field of your table visual and set it to show items when value =1.

I build a sample table to have a test.

Sample Table:

My YearSlicer is from 2000 to 2020.

Measure:

Measure = 
IF (
    SELECTEDVALUE ( 'YearSlicer'[Year] ) >= MAX ( 'Table'[Start Year] )
        && SELECTEDVALUE ( 'YearSlicer'[Year] ) <= MAX ( 'Table'[End Year] ),
    1,
    0
)

Result is as below.

When I select 2018, it should show values which ID = 1,2,3,6

You can download the pbix file from this link: Another slicer value between two column values post

Best Regards,

Rico Zhou

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

[AJTindall73]

Thank you Rico Zhou, that did the trick.... Brain fog had definatly kicked in yesterday.