Dynamic slicer value

[harshagraj]

Hi all i have created a below calclated column 

Partner =

VAR cnt =

COUNTX (

FILTER (

'OS Sales_Project_WonLost_VW',

'OS Sales_Project_WonLost_VW'[SF_Number]

= EARLIER ( 'OS Sales_Project_WonLost_VW'[SF_Number] )

&& 'OS Sales_Project_WonLost_VW'[Segment_code] = "0071"

),

'OS Sales_Project_WonLost_VW'[SF_Number]

)

VAR cnt1 =

COUNTX (

FILTER (

'OS Sales_Project_WonLost_VW',

'OS Sales_Project_WonLost_VW'[SF_Number]

= EARLIER ( 'OS Sales_Project_WonLost_VW'[SF_Number] )

&& 'OS Sales_Project_WonLost_VW'[Segment_code] <> "0071"

),

'OS Sales_Project_WonLost_VW'[SF_Number]

)

VAR C =

IF (

NOT ( ISBLANK ( cnt ) ) && NOT ( ISBLANK ( cnt1 ) ),

"Partner & Owner",

"Other"

)

RETURN

C

And I want to make [Segment_code] as per the slicer selection.
I have tried like this 'OS Sales_Project_WonLost_VW'[Segment_code] =Selectedvalue('OS Sales_Project_WonLost_VW'[Segment_code]) but it is not working.

Kindly help
@amitchandak @Greg_Deckler 

[amitchandak]

@harshagraj , You can not create  use slicer value in a column.

Create a measure and then do Segmentation if you need slicer on that

Single value example

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

Range example

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[harshagraj]

Hello @amitchandak thanks a lot for the reply but in my case this doesnt work. I have a slicer called segment codes and there are 30+ random 4 digit Alpanumeric characters are there. I have modified the code for region by combining Prjnum & Region and not able to change the values based on slicers.

Please suggest me the alternative method.

Partner =

VAR cnt =

COUNTX (

FILTER (

'OS Sales_Project_WonLost_VW',

'OS Sales_Project_WonLost_VW'[SF&Prj]

= EARLIER ( 'OS Sales_Project_WonLost_VW'[SF&Prj] )

&& 'OS Sales_Project_WonLost_VW'[Segment_code] = "0071"

),

'OS Sales_Project_WonLost_VW'[SF&Prj]

)

Var cnt1 =

COUNTX (

FILTER (

'OS Sales_Project_WonLost_VW',

'OS Sales_Project_WonLost_VW'[SF&Prj]

= EARLIER ( 'OS Sales_Project_WonLost_VW'[SF&Prj] )

&& 'OS Sales_Project_WonLost_VW'[Segment_code] <> "0071"

),

'OS Sales_Project_WonLost_VW'[SF&Prj]

)

RETURN

IF(NOT(ISBLANK(cnt)) && NOT(ISBLANK(cnt1)),"Partner & Owner","Other")

[Anonymous]

Hi @harshagraj ,

Here I suggest you to create an unrelated DimSegment_code table by dax.

DimSegment_code = VALUSE('OS Sales_Project_WonLost_VW'[Segment_code])

Then I think you need to create a measure to achieve your goal. As amitchandak  mentioned above, Power BI doesn't support us to get dynamic result by slicer or filter in calculated column. Measure to be a good workaround.

Please try this code to create a measure and create a table visual to get result.

Partner =
VAR _SELECTEVALUE =
    SELECTEDVALUE ( 'OS Sales_Project_WonLost_VW'[Segment_code] )
VAR cnt =
    COUNTX (
        FILTER (
            'OS Sales_Project_WonLost_VW',
            'OS Sales_Project_WonLost_VW'[SF&Prj]
                = MAX ( 'OS Sales_Project_WonLost_VW'[SF&Prj] )
                && 'OS Sales_Project_WonLost_VW'[Segment_code] = _SELECTEVALUE
        ),
        'OS Sales_Project_WonLost_VW'[SF&Prj]
    )
VAR cnt1 =
    COUNTX (
        FILTER (
            'OS Sales_Project_WonLost_VW',
            'OS Sales_Project_WonLost_VW'[SF&Prj]
                = MAX ( 'OS Sales_Project_WonLost_VW'[SF&Prj] )
                && 'OS Sales_Project_WonLost_VW'[Segment_code] <> _SELECTEVALUE
        ),
        'OS Sales_Project_WonLost_VW'[SF&Prj]
    )
RETURN
    IF (
        NOT ( ISBLANK ( cnt ) ) && NOT ( ISBLANK ( cnt1 ) ),
        "Partner & Owner",
        "Other"
    )

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.

[harshagraj]

Hi @Anonymous thanks a lot for the reply. I tried that disconnected table column as Slicer and created the below measure but there is no change. The slicer doesnt filter anything.
Am i doing correct? or is there anything to be changed in the code? because 

VAR _SELECTEVALUE =
    SELECTEDVALUE ( 'OS Sales_Project_WonLost_VW'[Segment_code] )

is refrerring to main table and not disconnected.