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
MattBI
Regular Visitor

Challenges with ALLSELECTED and iterators

So everything I've read says "stay away from ALLSELECTED inside iterators" because it has complex functionality and it probably won't do what you want it to (because it will activate the iterators shadow context). 

 

But...what if you need that functionality???  I'm looking for a way to achieve that result without ignoring best practices.

 

What I need:

* I have a measure "weight" which needs to calculate the current value over all of the currently sliced values

* Then I have a measure which uses "weight" which needs to calculate a value using an iterator because the total is non-additive

 

But:
* When I reference the "weight" measure from within the other measure within a SUMX it doesn't return the results I would expect. 

 

So ideally I'd like to avoid using ALLSELECTED (if that is indeed my problem) or use it in a safer way.


The below example is kind of a simplified version of what I'm looking at minus confidential info.

***MEASURE 1***

// Calculates the current row's volume against all of the other items currently selected by the slicer across all time.
// Working as expected

Weight =
DIVIDE(
    [Volume],
    CALCULATE(
        [Volume],
        ALLSELECTED(),
        REMOVEFILTERS(SourceData[Period])
    )
)

 

***MEASURE 2***

// Uses the weight above to apply a "benchmark".  Requires the use of an iterator because the totals are non-additive

// Unfortunately this behaves bizarrely.  When the columns of the summarize match the sliced columns, the totals are correct but the the total row isn't the sum of the rows.  When the summarize includes additional columns, the results are incorrect, but the total row is correct.  I'm pretty sure this is the result of interactions between the iterator and allselected, but i could be wrong abou that...

 

Weighted Volume =

VAR summarizeTable =
SUMMARIZE(
    SourceData,
    SourceData[Period],
    SourceData[client],
    Products[DESCRIPTION],
    Sales[DESCRIPTION]
)

RETURN
SUMX(summarizeTable, CALCULATE([Weight]*[Benchmark]))
4 REPLIES 4
hnguy71
Memorable Member
Memorable Member

Hi @MattBI ,

 

I don't think there's actually really an issue in using any functions within Power BI. Sure, there's always a preference over another but if your results are right and it doesn't bog down your model / visuals then go ahead and use it.

 

For your second measure, I would perhaps add the calculation within the summarize table such as this:

VAR summarizeTable =
SUMMARIZE(
    SourceData,
    SourceData[Period],
    SourceData[client],
    Products[DESCRIPTION],
    Sales[DESCRIPTION],
    "@Result", [Weight] * [Benchmark]
)

RETURN

SUMX(summarizeTable, [@Result])

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Hmmm...  that didn't return any results. 

I've heard that it's not a good pratice to create computed columns in a SUMMARIZE statement, so I added an ADDCOLUMNS to it, which worked, but unfortunately, that has the exact same behavior as my original measure definition.  

 @MattBI, I think the answer to this also is that it really just depends on the application. If you wanted to do just a basic output then yes ADDCOLUMNS is more preferred, however, if you want to do a categorical sum or rollup then SUMMARIZE alone is sufficient. I use both methodlogy just depends on what is required for my specific circumstances.

 

If by adjusting any formula or expression making it more complicated without any clear performance gains within your model then you're doing it wrong. Just my opinion.



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Thanks again for your response.  I hear what you're saying.  Unfortunately, as I mentioned above, using SUMMARIZE as you suggested returned no data for the rows in my output set.  Using ADDCOLUMNS instead returned results, but they were the same as what I was seeing originally so unfortunately, it didn't help improve the situation.  

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.