Forum Discussion
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 Alloc...
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]))
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.