topic Summarize output of dynamic measure in DAX Commands and Tips

Summarize output of dynamic measure

Anonymous — Fri, 02 Jul 2021 09:45:09 GMT

Hi all,

I am having following problem: I try to dynamically categorise skus to a specific bin type based on their size metrics. The available bin types change dynamically so I created a dropdown list to select the bin types which should be taken into consideration in the measure.

Following measure is used to categorise the SKUs:

BinTypeMeasure =
VAR SKU_Vol = SELECTEDVALUE (SKU[Repl_Vol])
Var length = SELECTEDVALUE(SKU[Length])
Var width = SELECTEDVALUE(SKU[Width])
Var Height = SELECTEDVALUE(SKU[Height])
VAR Boundary = SELECTCOLUMNS( TOPN ( 1, FILTER ( ALLSELECTED(Bin_Types), Bin_types[Volume]>= SKU_Vol && Bin_types[Height]>= Height && Bin_types[Width]>=width && Bin_types[Length]>= length), Bin_types[Volume], ASC),"Result",Bin_Types[Bin type])
Return
Boundary

Unfortunately this measure returns the output on SKU basis only

What I want however is a count of BinTypeMeasure, to stick to the example above something like this:

S06	5
B06	1

Your help is greatly appreciated.

If the powerBI file is needed feel free to contact me

Re: Summarize output of dynamic measure

daxer-almighty — Sat, 03 Jul 2021 16:46:04 GMT

[BinType Count] = switch( true(), ISINSCOPE( Bin_Types[Bin Type] ), var BinType_ = SELECTEDVALUE( Bin_Types[Bin Type] ) return COUNTROWS( FILTER( DISTINCT( SKU[SKU] ), [BinTypeMeasure] = BinType_ ) ), // If we're on a total row or // if there's no bin type in scope... SUMX( ALLSELECTED( Bin_Types ), var BinType_ = Bin_Types[Bin Type] return COUNTROWS( FILTER( DISTINCT( SKU[SKU] ), [BinTypeMeasure] = BinType_ ) ) ) ) // Version 2: might be faster... [BinType Count ver.2] = var BinTypesWithCounts = CALCULATETABLE( ADDCOLUMNS( VALUES( Bin_Types[Bin Type] ), "@Count", var BinType_ = Bin_Types[Bin Type] return COUNTROWS( FILTER( DISTINCT( SKU[SKU] ), [BinTypeMeasure] = BinType_ ) ) ), ALLSELECTED( Bin_Types ) ) var Result = IF( ISINSCOPE( Bin_Types[Bin Type] ), var CurrentBinType = SELECTEDVALUE( Bin_Types[Bin Type] ) return MAXX( FILTER( BinTypesWithCounts, Bin_Types[Bin Type] = CurrentBinType ), [@Count] ), SUMX( BinTypesWithCounts, [@Count] ) ) return Result

Re: Summarize output of dynamic measure

Anonymous — Mon, 05 Jul 2021 12:48:19 GMT

Works like a charm thank you four your help.