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

Question

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 · Answer

PhilSmith&nbsp;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/ba-p/279563

philsmith · Answer

Thank you.&nbsp; 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.&nbsp;Sales Item Descitem_id Sale 1 Box1 $345 2 Bag1 $543 3 Tub3 $75 4 Crate4 $54432 $5431 $43&nbsp;Item Desc1 Box2 Bag3 Tub4 Crate&nbsp;Related by item_id&nbsp;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.&nbsp; 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):&nbsp;ABC_Classes =&nbsp;IF( HASONEVALUE(Sales[item_id]),IF(HASONEVALUE(Sales[ABC_Class_by_Item]),SWITCH(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VALUES(Sales[ABC_Class_by_Item]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "A",[ABC_Class_A_by_Item],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "B",[ABC_Class_A_by_Item],"C",[ABC_Class_C_by_Item]),"OutOfBounds"))&nbsp;The following measure is like Attendance in the example, and returns A, B, or C&nbsp;ABC_Class_by_Item =&nbsp;IF (&nbsp; &nbsp; HASONEVALUE ( 'item'[item_id] ),&nbsp; &nbsp; VAR SalesByItem =&nbsp; &nbsp; &nbsp; &nbsp; CALCULATETABLE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ADDCOLUMNS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE ( Sales, 'item'[item_id] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "@ProdSales", [Sale]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED ( 'item' )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR AllSales =&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Sale],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED ( 'item' )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR CurrentSalesAmt = [Sale]&nbsp; &nbsp; VAR CumulatedSales =&nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SalesByItem,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [@ProdSales] &gt;= CurrentSalesAmt&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR CumulatedSalesAmount =&nbsp; &nbsp; &nbsp; &nbsp; SUMX (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CumulatedSales,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [@ProdSales]&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR CurrentCumulatedPct =&nbsp; &nbsp; &nbsp; &nbsp; DIVIDE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CumulatedSalesAmount,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AllSales&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR Result =SWITCH (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TRUE,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ISBLANK ( CurrentCumulatedPct ), BLANK (),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CurrentCumulatedPct &lt;= 0.8, "A",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CurrentCumulatedPct &lt;= 0.9, "B",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "C"&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;RETURN&nbsp; &nbsp; &nbsp; &nbsp; Result)&nbsp;Individual Measures for A,B, and C that return the Class or Blank, much like Attended and Not Attended.&nbsp; For instance:ABC_Class_A_by_Item =&nbsp;IF (&nbsp; &nbsp; HASONEVALUE ( 'item'[item_id] ),&nbsp; &nbsp; VAR SalesByProduct =&nbsp; &nbsp; &nbsp; &nbsp; CALCULATETABLE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ADDCOLUMNS (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE ( Sales, 'item'[item_id] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "@ProdSales", [Sale]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED ( 'item' )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR AllSales =&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Sale],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED ( 'item' )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR CurrentSalesAmt = [Sale]&nbsp; &nbsp; VAR CumulatedSales =&nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SalesByProduct,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [@ProdSales] &gt;= CurrentSalesAmt&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR CumulatedSalesAmount =&nbsp; &nbsp; &nbsp; &nbsp; SUMX (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CumulatedSales,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [@ProdSales]&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR CurrentCumulatedPct =&nbsp; &nbsp; &nbsp; &nbsp; DIVIDE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CumulatedSalesAmount,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AllSales&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; VAR Result =SWITCH (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TRUE,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ISBLANK ( CurrentCumulatedPct ), BLANK (),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CurrentCumulatedPct &lt;= 0.8, "A",&nbsp; &nbsp; &nbsp; &nbsp; BLANK()&nbsp; &nbsp;)&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; RETURN&nbsp; &nbsp; &nbsp; &nbsp; Result)&nbsp;Any ideas why?&nbsp;

Forum Discussion

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

2 Replies

Related content

Relationship

Relationships

Relationship Issue

Creating relationships

PowerBI Relationships