Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Summarize output of dynamic measure

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 

 

Steffen061_0-1625218467866.png

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

S065
B061

 

Your help is greatly appreciated. 

 

If the powerBI file is needed feel free to contact me

1 ACCEPTED SOLUTION
daxer-almighty
Solution Sage
Solution Sage

[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

View solution in original post

2 REPLIES 2
daxer-almighty
Solution Sage
Solution Sage

[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
Anonymous
Not applicable

Works like a charm thank you four your help.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors