Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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
Solved! Go to Solution.
[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
[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
Works like a charm thank you four your help.