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 Sony Samsung Apple Nokia Redmi   here i have selected Apple in the slic...
Need Help
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.