Change value based on slicer option

[nailifarhanah]

HIi,

I have a set of data that as below and I have a another table for the slicer YA data only. I've managed to create the relationship for these 2 from the YA fields:

YA	Status	Type	Project
2021	Incomplete	A	A1
2021	Complete	A	A2
2022	Complete	A	A3
2022	Complete	B	B1

Slicer table

YA

2021

2022

I want to create a column named [New YA] and it will change its value based on the slicer selected. 

For example if i choose from the slicer 2022, those 2021 projects that are incomplete will be tagged as 2022 in the [New YA] column ie the slicer option instead of 2021.

The logic is that these prior incomplete projects as at 2022 are now brought forward and included in the YA 2022 list.

Otherwise it will just follow the original YA.

Ultimately, the new data table will look like this.

	Slicer (Year)	2021
Data
YA	Status	Type	Project	New YA if 2021 slicer is chosen
2021	Incomplete	A	A1	2021
2021	Complete	A	A2	2021
2022	Complete	A	A3	2022
2022	Complete	B	B1	2022

	Slicer (Year)	2022
YA	Status	Type	Project	New YA if 2022 slicer is chosen
2021	Incomplete	A	A1	2022
2021	Complete	A	A2	2021
2022	Complete	A	A3	2022
2022	Complete	B	B1	2022

Hope I made this simple to understand.

How do i do this in power BI?

Thank you.

[v-easonf-msft · Accepted solution]

Hi, @nailifarhanah 

As a workaround, you can add a measrue like:

New YA = 
VAR _original =
    SELECTEDVALUE ( 'Table'[YA] )
VAR _slicer =
    SELECTEDVALUE ( 'Slicer table'[YA] )
VAR _status =
    SELECTEDVALUE ( 'Table'[Status] )
RETURN
    IF (
        ISFILTERED ( 'Slicer table'[YA] ),
        IF ( _original < _slicer && _status = "Incomplete", _slicer, _original ),
        _original
    )

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Calculated column formulas do not respond to a change in slicers.