SWITCH function not switching

[ofoster]

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.

[amitchandak]

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

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.