Forum Discussion

ofoster's avatar
ofoster
Frequent Visitor
3 years ago

SWITCH function not switching

Hello,

 

I am trying to write a function that will switch columns based on a given slicer selection.

 

My slicer is a disconnected table that has only two variables in it: Allocated and Not Allocated.

The two columns I am switching between are as follows:

 

Consignee Parent: is from a database so it is just a list of parents

Allocated Consignee Parent: a column from Power Query in which I duplicated Consignee Parent and replaced "X Distribution" with BLANK().

 

Here is my switching function (a calculated column) in question:

W/WO X = SWITCH(SELECTEDVALUE('Allocation'[Commerce Allocation]),"Allocated", 'Consignee Parent'[Allocated Consignee Parent],  'Consignee Parent'[Consignee Parent])



The problem is that even though I am engaging the slicer, it remains on Consignee Parent the entire time.

 

What I'm wanting it to do is either remove or add X Distribution to the rows of my matrix.

 

Thank you and I look forward to your responses.

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    ofoster , A calculated column can not use a slicer value.

    You can have table with these two values and use the value to switch the measures

     

    example

    W/WO X = SWITCH(SELECTEDVALUE('Allocation'[Commerce Allocation]),"Allocated", count('Consignee Parent'[Allocated Consignee Parent]), Count('Consignee Parent'[Consignee Parent]))

    • ofoster's avatar
      ofoster
      Frequent Visitor
      3 years ago

      I used this solution as a jumping board to make a usable solution. I created a measure in the same format, but instead of the columns, "Allocated Consignee Parent" and "Consignee Parent", I created two calculated columns "Alllocated Binary" and "Binary".

       

      Their equations are as follows:

      Binary = IF('Consignee Parent'[Consignee Parent] <> BLANK(), 1, 0)
      Allocated Binary = IF('Consignee Parent'[Allocated Consignee Parent] = BLANK(), 0, 1)
      ** I probably could have simplified Binary but I was tired and it worked.


      I then created the following measure:
      x = SWITCH(SELECTEDVALUE('Allocation'[Commerce Allocation]), "Allocated", SUM('Consignee Parent'[Allocated Binary]), SUM('Consignee Parent'[Binary]))
       
      When I put this in as a value in a matrix, and tell it to take the average value of this calculation, every row is either assigned 0 or 1 depending on how I filter it.
       
      I then on the filters of the page tell it that if "x" is NOT  0, keep it, otherwise pull it from the visual. It does take Power BI a hot minute to do this, but it does work while taking up minimal space on the visual.