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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kthxbye
Frequent Visitor

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

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:

 

kthxbye_0-1628114080943.png

 

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:

 

kthxbye_1-1628114213520.png

kthxbye_2-1628114260973.png

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!

1 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.

View solution in original post

7 REPLIES 7
wdx223_Daniel
Super User
Super User

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]
    )

 

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.

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

kthxbye_0-1628194771188.png

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).

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.

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

Mohammad_Refaei
Solution Specialist
Solution Specialist

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors