TREATAS working as a calculate filter in DAX Studio but not Power BI Desktop

[kthxbye]

Ok, here goes:

I want to create a measure in Power BI Desktop that is SUM ( 'fact Back Order Demand'[Ordered Units Backordered] ) *but* only returns a value for Items (products) with the Top N number of backordered units. The following query in DAX Studio works exactly how I want it to:

EVALUATE
VAR Top_N =
    MAX ( 'TopN'[TopN] )
VAR filtered_items =
    SELECTCOLUMNS (
        TOPN (
            Top_N,
            SUMMARIZE (
                'fact Back Order Demand',
                'fact Back Order Demand'[OrderedItemKey],
                "OUBamt", SUM ( 'fact Back Order Demand'[Ordered Units Backordered] )
            ),
            [DUBamt]
        ),
        "OrderedItemKey", [OrderedItemKey]
    )
RETURN
    SUMMARIZECOLUMNS (
        'Item Details (Ordered Items)'[Item Description],
        "OUB",
            CALCULATE (
                SUM ( 'fact Back Order Demand'[Ordered Units Backordered] ),
                TREATAS ( filtered_items, 'fact Back Order Demand'[OrderedItemKey] )
            )
    )

 I can set Top_N = 5, and the query returns the following:

There's a tie, so it returns 6 records, but it's acting exactly how I want. So what's the problem? I *cannot* get the measure to function in PBI Desktop:

It returns data for all items, not just the Top 5! As far as I can tell, TREATAS is not working to filter the CALCULATE(SUM()) but I don't know why. What am I missing? Is there any alternative solution? Thanks!

[wdx223_Daniel]

because with the filter context, each row only have one item.

try to change the code of filtered_items

VAR filtered_items =
    SELECTCOLUMNS (
        TOPN (
            Top_N,
            SUMMARIZE (
                ALLSELECTED('fact Back Order Demand'),
                'fact Back Order Demand'[OrderedItemKey],
                "OUBamt", SUM ( 'fact Back Order Demand'[Ordered Units Backordered] )
            ),
            [DUBamt]
        ),
        "OrderedItemKey", [OrderedItemKey]
    )

[kthxbye · Accepted solution]

Demand Units Backordered = 
        CALCULATE (
            SUM ( 'fact Back Order Demand'[Ordered Units Backordered] ),
            TREATAS (
                SELECTCOLUMNS (
                    TOPN (
                        5,
                        SUMMARIZE (
                            ALLSELECTED ( 'bridge Build of Materials' ),
                            'bridge Build of Materials'[OrderedItemKey],
                            "OUB", SUM ( 'fact Back Order Demand'[Ordered Units Backordered] )
                        ),
                        [OUB]
                    ),
                    "OrderedItemKey", [OrderedItemKey]
                ),
                'fact Back Order Demand'[OrderedItemKey]
            )
        )

I finally got there in the end ... this measure works, and it's performance isn't too slow.

[kthxbye]

You're definitely right, there's a problem with the filtered_items filter context:

In this case, when I just want to count the number of items being returned, it's showing the correct number for the Total (5) and then seemingly ignoring the Top_N filter for the rest and returning the total number of items (87).

[kthxbye]

I see what you're saying.... however, adding an ALLSELECTED() to the 'fact Back Order Demand' table did not resolve the issue. The measure in Power BI is still returning a value for all items, not just the Top N. I will look at other ways to adjust the filter context for filtered_items.

[kthxbye]

I feel like there's something here with an iterator function, perhaps a SUMX()?

[Mohammad_Refaei]

What is source table of the [Item Description] in your matrix?

[kthxbye]

'Item Details (Ordered Items)'[Item Description]