Forum Discussion

dganeswararao's avatar
dganeswararao
New Member
6 years ago

Create text column based on slicer selection

Hello All

 

can anyone please help me in creating new text column based on slicer selection like below

Slicer
SonySamsungAppleNokiaRedmi

 

here i have selected Apple in the slicer, then my new column MaskName should be like below. other names should be masked.

this new column should be dymanic based on slicer selection. 

 

NameProductionMaskName
Sony100A
Samsung200B
Apple5420Apple
Nokia254C
Redmi365D

 

thanks in advance

Ganesh

Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    Columns are static. You cannot create a column based on a slicer selection.  What you can do is create a measure.

     

    Naming the other vendors A, B, C, D etc can be done by using a VALUES() list that needs to exclude the current selection.

     

    NOTE: The slicer needs to be fed from an independent table that is not connected to the other tables in the data model.

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      6 years ago

      Quick question - Does it have to be A, B, <vendor>, C, D etc  or can it also be A, B, <vendor>, D, E  etc?  That would be much easier to implement.

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        6 years ago

        If that is acceptable then here is a possible solution.  It involves the use of RANKX to create a number sequence and then some character trickery to arrive at the desired outcome. This also limits you to 26 partners in the list 🙂

         

         

         

        Measure = 
// partner list with sales
var r1 = SELECTEDVALUE('FactTable'[Partner])
// reference tabe just with unique partner names - this feeds the slicer
var r2 = SELECTEDVALUE('Reference'[Partner])
// RANKX returns 1,2,3 etc. We convert that to A, B, C etc
var g = rankx(allselected('FactTable'[Partner]),[v])+64
var r =  if(HASONEVALUE('FactTable'[Partner]),
       //show the selected partner name if it matches
       if(r1=r2,r1,
       // mask the other partners
       "Partner " & UNICHAR(g)),
       // suppress the total as it makes no sense
       BLANK())
return r

         

         

        NOTE:  Having done similar exercises in the past I need to warn you. Partners are very smart. They figure out who "Partner C"  is in less time than it takes you to send the obfuscated data to them. You may want to apply some randomness to the process and - for example - add a calculated column to your fact table 

         

        r = rand()

         

        and then change the above measure v to say

         

        v := average(r)

         

        That will give you a different sort order of the partners every time you refresh the data source.