Hi
I have a switch statement in a calculated column and want to use a measure from a slicer as boundaries of rules. It works when the boundaries are numbers.
When the boundaries are a measure the switch statement ignores the measure. I have tried it when the measure is derived from a slicer using parameters …and tried using a normal slicer.
I have tested the values of the slicer using a card and that is working.
This works:
Segment_Switch =
VAR
Rule1= 25000<=[Total Profits] && [Total Profits]<=1000000
VAR
Rule2 = 20000<=[Total Profits] && [Total Profits]<25000
VAR
Rule3 = 10000<=[Total Profits] && [Total Profits]<20000
RETURN
SWITCH( TRUE(),
Rule1 , "Top Client",
// 25000<[Total Profits] && [Total Profits]<=1000000 , "Good Client",
Rule2, "Good Client",
Rule3,"OK Client",
"Poor")
Below ignores the Rule 1 and 2 , where Select Limit is used as a measure to make the boundary selectable.
Segment_Switch =
VAR
Rule1= [Select_Limit]<=[Total Profits] && [Total Profits]<=1000000
VAR
Rule2 = 20000<=[Total Profits] && [Total Profits]<[Select_Limit]
VAR
Rule3 = 10000<=[Total Profits] && [Total Profits]<20000
RETURN
SWITCH( TRUE(),
Rule1 , "Top Client",
// 25000<[Total Profits] && [Total Profits]<=1000000 , "Good Client",
Rule2, "Good Client",
Rule3,"OK Client",
"Poor")
Where [Select_Limit] is a measure from a slicer , and I chose a value 25000 so it should give the same result as above. It however ignores Rule1 when I do this, all the other rules work.
Not only do I need a solution or work around , I would like to know why switch cant be used with measures (is this a bug, or am I misunderstanding DAX logic?) I even tried to put calculate around the measure - no luck) 
Many Thanks
Graham
Just to add that when the measure is a constant is works …its just a hassle to keep updating the measure rather than it coming from a slicer
I’m very surprised a slicer selection can be integrated into a calculated column. Do you mean a measure?
SWITCH works fine in measures. That’s predominately where I would use them.
I think you should be avoiding calculated columns with this totally.
Can you place some images of how your using this one.
Also maybe send a link to the pbix file.
I don’t think this is very difficult to fix, I just need a little more context. Thanks
Just reviewing your file
As I thought your idea of using the slicer within the calculated column isn’t doing anything.
This part of the formula I believe is just evaluating to 25K everytime because you have placed that number as the alternative result.
A slicer selection is only valid for that report page. It doesn’t carry over to other report pages or into calculated columns.
Calculated columns can’t also be dynamically change by selections in reports. They only update on refreshing of your report.
I’ll keep looking and see if there is a better way to achieve what you need. It’s likely centered around the dynamic grouping concept.
Logic similar to this is what you need and you will need to incorporate the slicer amount into the formula somehow
It looks like you already have some table set up for do this in your model
Let me know how you go with this.
The key here is just to complete this dynamically rather than physically creating a column in your table.
Based on the logic you need I believe this is the only way you can get this done.
I Progressed at this by using say a dynamic table ( a subset of a main table) using a filter function). While it is more efficient than using the switch , you cant filter a table using a measure from slicer , you can use a measure as a constant to filter the table on one of the table columns (here Segment Code) . The problem is that I have to update the measure manually ie in the edit line. Is there a way to update the measure through a text box on the canvas.
So I am one step further !
Pic|690x360
The restriction here still though for a dynamic table is that you would always need to refresh you report to get it working.
I think this can all be done with measure but I struggling to get my head around everything that’s required.
From my knowledge you can’t capture anything from a text box in a measure.
