Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |