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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.