Slicing by dynamic label

[istudent]

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 have a Products table and a Sales table in a one-to-many relationship. Each transaction in the Sales table takes place at a location, which is stored as an attribute in the table. For each product, I want to label it as "Successful" or "Unsuccessful" depending on the volume of transactions, which can be further broken down by location. The largest obstacle is that users of the report can also select date ranges. Product A might have been unsuccessful at location B until 6/18/2019, where it received enough sales to place it in the "Successful" bucket. Consequently at refresh time I cannot apply the label to the product since date selections can affect its value.

If I use a matrix with the products as rows and the locations as columns, I can use a measure to correctly label each product-location combination. I need to go the other direction, however, and select products that were "Successful" at location A but "Unsuccessful" at location B, like one may do with a slicer, filtering other visuals to these products. How can I achieve this behavior?

[v-juanli-msft · Accepted solution]

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.