topic TREATAS working as a calculate filter in DAX Studio but not Power BI Desktop in DAX Commands and Tips

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

kthxbye — Wed, 04 Aug 2021 21:57:56 GMT

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!

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

Mohammad_Refaei — Thu, 05 Aug 2021 03:19:48 GMT

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

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

wdx223_Daniel — Thu, 05 Aug 2021 03:22:10 GMT

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

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

kthxbye — Thu, 05 Aug 2021 16:45:53 GMT

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.

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

kthxbye — Thu, 05 Aug 2021 16:46:34 GMT

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

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

kthxbye — Thu, 05 Aug 2021 18:13:29 GMT

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

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

kthxbye — Thu, 05 Aug 2021 20:19:55 GMT

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

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

kthxbye — Fri, 06 Aug 2021 00:28:45 GMT

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.