Calculation runs out of Resources. Iterating over items?

[Luisv]

Hello everyone, 

I’ve recently worked out a series of Dax calculations that work with a small sample dataset, but they eat through all the available resources and fail when they run through the real data. I’m hoping that a best practice or alternate method exists so that I can get past this issue!

I’m trying to filter and keep only the Orders with a milestone at or after the selected year. Orders have a Milestone column with appropriate date values in a Date column, though I'm using normal numbers in this sample data. The idea is that when a "date" is equal to or greater than the selected "date" for the selected milestone, the entire Order is displayed. The sequence is as follows - Capture the selected Milestone and "date" (columns "Selected Milestone" and "Selected Date"), pinpoint and test the date value for the selected milestone against the selected date ("Date" check column), and then figure out what Orders passed the test and which ones did not. So I need to be able to keep an entire order where the selected milestone passed, and not just the one milestone out of the set. 

In the attached pbix the uncolored columns are the raw sample data, the green columns are the intermediary steps, and the amber column titled "Orders that Passed" is the final output. My intention is to put "Orders that Passed" in the filters to remove the failing Orders, filtering out the "-" values. 

I get the impression that there's a lot of recursion in the final step or two, with a mixture of CALCULATE(), FILTER(), and ALLEXCEPT().  The hope is that this is not a new issue to the community and there's a leaner way of getting this functionality to work, or a best practice that I'm too new to understand yet.  For context, I've been doing dashboards for several years with Tableau and have been working with PowerBI for 4 months, and this is the sort of thing I would have solved with a Level of Detail expression in the other program. 

Thank you! 

Best, 

Luis

File: Milestone LoD workout.pbix I don't see other options for sharing the pbix file, please let me know if this doesn't work and any other comments! 

[Anonymous · Accepted solution]

Hi @Luisv ,

Please try the following measure.

MEASURE = 
VAR _MileStone =
    SELECTEDVALUE ( 'Letter Table'[Column1] )
VAR _Date =
    MAX ( 'New Table'[Column1] )
VAR _vtable =
    SELECTCOLUMNS (
        FILTER (
            ALLSELECTED ( 'Milestone Test' ),
            'Milestone Test'[Milestone] = _MileStone
&& 'Milestone Test'[Fake "Date"] > _Date
        ),
        "_Order", 'Milestone Test'[Order]
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Milestone Test'[Order] ) IN _vtable,
        CONCATENATEX (
            FILTER ( _vtable, [_Order] = SELECTEDVALUE ( 'Milestone Test'[Order] ) ),
            [_Order]
        ),
        "-"
    )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.