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

      • harshagraj's avatar
        harshagraj
        Post Partisan
        4 years ago

        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.