HowTo put Measure in slicer or in relationship (Dynamic application)

[PhilSmith]

I have a measure Speed that evaluates to "Low", Medium", or "High" for each ITEM depending on sales volume.  This can't be in a calculated table because we need to choose different sets of customers, different date ranges, etc.  I also have an ABC ranking that has the same issue:  A measure that breaks down to "A","B","C", but it can't be in a calculated table because of the need for slicers for customers, dates, etc.

A slicer will not take the measure, and I can't create a relationship to a static table with just those choices, presumably for the same reasons.

Anyone know the magic behind this one?

Thanx

Phil

[Greg_Deckler]

@PhilSmith In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[PhilSmith]

Thank you.  I think I understand this, but something is wrong when I try to apply it.

Lets go with the ABC problem, as it is straightforward.

Sales Item Desc

item_id Sale 1 Box

1 $345 2 Bag

1 $543 3 Tub

3 $75 4 Crate

4 $5443

2 $543

1 $43

Item Desc

1 Box

2 Bag

3 Tub

4 Crate

Related by item_id

I built a measure that gives me the class, (ABC_Class_by_Item, Returns A, B or C) then I built a measure for each class that returns the class or blank.  All works as expected. The measure that "ties it all together," doesn't like Sales[ABC_Class_by_Item], (cannot be found or may not be used):

ABC_Classes = 

IF( HASONEVALUE(Sales[item_id]),

IF(HASONEVALUE(Sales[ABC_Class_by_Item]),

SWITCH(

          VALUES(Sales[ABC_Class_by_Item]),

          "A",[ABC_Class_A_by_Item],

          "B",[ABC_Class_A_by_Item],

"C",[ABC_Class_C_by_Item]

),

"OutOfBounds"

)

)

The following measure is like Attendance in the example, and returns A, B, or C

ABC_Class_by_Item = 

IF (

    HASONEVALUE ( 'item'[item_id] ),

    VAR SalesByItem =

        CALCULATETABLE (

            ADDCOLUMNS (

                SUMMARIZE ( Sales, 'item'[item_id] ),

                "@ProdSales", [Sale]

            ),

            ALLSELECTED ( 'item' )

        )

    VAR AllSales =

        CALCULATE (

            [Sale],

            ALLSELECTED ( 'item' )

        )

    VAR CurrentSalesAmt = [Sale]

    VAR CumulatedSales =

        FILTER (

            SalesByItem,

            [@ProdSales] >= CurrentSalesAmt

        )

    VAR CumulatedSalesAmount =

        SUMX (

            CumulatedSales,

            [@ProdSales]

        )

    VAR CurrentCumulatedPct =

        DIVIDE (

            CumulatedSalesAmount,

            AllSales

        )

    VAR Result =SWITCH (

            TRUE,

            ISBLANK ( CurrentCumulatedPct ), BLANK (),

            CurrentCumulatedPct <= 0.8, "A",

            CurrentCumulatedPct <= 0.9, "B",

            "C"

        )

           RETURN

        Result

)

Individual Measures for A,B, and C that return the Class or Blank, much like Attended and Not Attended.  For instance:

ABC_Class_A_by_Item = 

IF (

    HASONEVALUE ( 'item'[item_id] ),

    VAR SalesByProduct =

        CALCULATETABLE (

            ADDCOLUMNS (

                SUMMARIZE ( Sales, 'item'[item_id] ),

                "@ProdSales", [Sale]

            ),

            ALLSELECTED ( 'item' )

        )

    VAR AllSales =

        CALCULATE (

            [Sale],

            ALLSELECTED ( 'item' )

        )

    VAR CurrentSalesAmt = [Sale]

    VAR CumulatedSales =

        FILTER (

            SalesByProduct,

            [@ProdSales] >= CurrentSalesAmt

        )

    VAR CumulatedSalesAmount =

        SUMX (

            CumulatedSales,

            [@ProdSales]

        )

    VAR CurrentCumulatedPct =

        DIVIDE (

            CumulatedSalesAmount,

            AllSales

        )

    VAR Result =SWITCH (

            TRUE,

            ISBLANK ( CurrentCumulatedPct ), BLANK (),

            CurrentCumulatedPct <= 0.8, "A",

        BLANK()   )

    RETURN

        Result

)

Any ideas why?