Forum Discussion
istudent
7 years agoFrequent Visitor
Slicing by dynamic label
Essentially, I need to be able to slice a table using a measure. A calculated column is infeasible because the label for each row depends on date selections. The situation is similar to this: I h...
v-juanli-msft
7 years agoCommunity Support
Hi istudent
istudent wrote:
Essentially, I need to be able to slice a table using a measure.
It is impossible to use a measure in a slicer visual.
Here is a workaround:
1.
create two tables
successful location = VALUES('sales table'[location])
unsuccessful location = VALUES('sales table'[location])
don't create any relationship for the two tables
2. create measures in "sales table"
sales_selected =
CALCULATE (
SUM ( 'sales table'[sale] ),
FILTER (
ALLSELECTED ( 'sales table' ),
'sales table'[product id] = MAX ( 'sales table'[product id] )
&& 'sales table'[location] = MAX ( 'sales table'[location] )
)
)
target = 5
(assume target sales=5)
if_su = IF([sales_selected]>=[target],"successful","unsuccessful")
sucess_location = SELECTEDVALUE('successful location'[location])
unsucess_location = SELECTEDVALUE('unsuccessful location'[location])
flag1 =
CALCULATE (
MAX ( 'sales table'[location] ),
FILTER (
ALLSELECTED ( 'sales table' ),
'sales table'[product id] = MAX ( 'sales table'[product id] )
&& 'sales table'[location] = MAX ( 'sales table'[location] )
&& [if_su] = "successful"
)
)
flag2 =
CALCULATE (
MAX ( 'sales table'[location] ),
FILTER (
ALLSELECTED ( 'sales table' ),
'sales table'[product id] = MAX ( 'sales table'[product id] )
&& 'sales table'[location] = MAX ( 'sales table'[location] )
&& [if_su] = "unsuccessful"
)
)
condition = IF([flag1]=[sucess_location]||[flag2]=[unsucess_location],1,0)
3.
add "location" from "successful location" table and "unsuccessful location" table to two slicers,
add "condition" from "sales table" to the Visual level filter of the matrix visual
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.