Forum Discussion

chotu27's avatar
chotu27
Post Patron
6 years ago
Solved

replacing column values based on slicer slection

 

Sample data:

CategoryDatamaskingValues
Intensive Caresub367
Laon Caresub123
Medical Caresub235

 

 

 

 

 

Output required:   Suppose user selects Loancare from category slicer Output  column should be shown as below same for all the Category values in slicer.

 

Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi chotu27

    I build a table like yours to have test.

    We need to build a new Slicer table by Category column.

    Slicer = VALUES('Table'[Category])

     

    Then build a slicer.

    Build a measure to achieve your goal.

    Output =

VAR _selectvalue =

    SELECTEDVALUE ( 'Slicer'[Category] )

RETURN

    IF (

        MAX ( 'Table'[Category] ) = _selectvalue,

        MAX ( 'Table'[Category] ),

        CALCULATE (

            MAX ( 'Table'[Datamasking] ),

            FILTER ( ALL ( 'Table' ), 'Table'[Category] = MAX ( 'Table'[Category] ) )

        )

    )

     

    Result:

    Default:

    Select Laon Care:

    You can download the pbix file from this link: Replacing column values based on slicer slection

     

    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. 

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi chotu27

    I build a table like yours to have test.

    We need to build a new Slicer table by Category column.

    Slicer = VALUES('Table'[Category])

     

    Then build a slicer.

    Build a measure to achieve your goal.

    Output =

VAR _selectvalue =

    SELECTEDVALUE ( 'Slicer'[Category] )

RETURN

    IF (

        MAX ( 'Table'[Category] ) = _selectvalue,

        MAX ( 'Table'[Category] ),

        CALCULATE (

            MAX ( 'Table'[Datamasking] ),

            FILTER ( ALL ( 'Table' ), 'Table'[Category] = MAX ( 'Table'[Category] ) )

        )

    )

     

    Result:

    Default:

    Select Laon Care:

    You can download the pbix file from this link: Replacing column values based on slicer slection

     

    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. 

    • chotu27's avatar
      chotu27
      Post Patron
      6 years ago

      Anonymous  Thank you for your help i have a small questions 

       Q1 :  If i remove Datamasking column from table it is not showing me the same results.

       Q2:   What if the user selects multiple values from slicer ? is there any possibilty to display multiple values selected in output?

    • chotu27's avatar
      chotu27
      Post Patron
      6 years ago

      Anonymous Greg_Deckler 

       

      Can i Use Output Measure DAX in calculated column instead, beacause i wanted to use this column in some charts as legend

       

       

       

      Thanks

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi chotu27 

        I build a new measure to you and you can display multiple values selected in output.

         

        Output = 
VAR _selectvalue =
    ALLSELECTED ( 'Slicer'[Category] )
VAR _DateMasking =
    CALCULATE (
        MAX ( 'Table'[Datamasking] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Category] = MAX ( 'Table'[Category] ) )
    )
RETURN
    IF (
        ISFILTERED ( Slicer[Category] ),
        IF (
            MAX ( 'Table'[Category] ) IN _selectvalue,
            MAX ( 'Table'[Category] ),
            _DateMasking
        ),
        _DateMasking
    )

         

        You can use Ctrl+left click the box to select the multiple value.

        Result:

        And if you want to remove Datamasking column from table visual, you need to make value column not summarize.

        Result:

        Calculated column can not show dynamic values like measure, but a measure can't be added into the legend.

        I try to build a matrix visual to work like a legend.

        You need to add color in conditional formatting and change the column header's and Grid 's color to white.

        Result:

        You can download the pbix file from this link: replacing column values based on slicer slection

         

        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. 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    chotu27 - Not quite following this. 

     

    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

     

    That said, this might help: https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-EVERYTHING-measures-axis-legend-titles-chart-types/m-p/1027881#M444