Forum Discussion

harshagraj's avatar
harshagraj
Post Partisan
4 years ago

Dynamic slicer value

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.
 
 
Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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/1387187#M626


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

    • harshagraj's avatar
      harshagraj
      Post Partisan
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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.