cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chotu27
Post Patron
Post Patron

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.Capture 2.PNG

 

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @chotu27

I build a table like yours to have test.

1.png

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:

2.png

Select Laon Care:

3.png

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. 

View solution in original post

6 REPLIES 6
RicoZhou
Community Support
Community Support

Hi @chotu27

I build a table like yours to have test.

1.png

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:

2.png

Select Laon Care:

3.png

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. 

View solution in original post

@RicoZhou @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

RicoZhou
Community Support
Community Support

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:

1.png

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

2.png

Result:

3.png

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.

4.png

Result:

5.png

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. 

@RicoZhou  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?

Greg_Deckler
Super User
Super User

@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-titl...


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@Greg_Deckler  Can you check now

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.