Is this bad DAX, even though it works

[rpiboy_1]

I know that I always read about how tricky calculate can be and avoiding 'nested' calculates, so I'm looking for feedback. I have a measure that works, but there are nested Calculates, so I want to know if there is a better way.

Model set-up
table: Dates (well formed via Bravo)
table: Projects

Projects has a column 'Created Date' which is related to 'Date'[Date] via an inactive relationship.

I'm returning the average number of projects created per month for the current year, so for 2025 that would be 'Count Projects' / 'Count Months' (2) (January & February).

Here is the measure that works, but importantly you'll note that I'm nesting another measure [Projects Created] which itself is wrapped in a calculate. If I try to remove the calculate that wraps _result I get an OLE DB / ODBC error which cites [Projects Created] so I assume it has to do with the in-active relationship. If I try to direclty write the contents of the [Projects Created] measure into this measure it also fails.

Main Measure:

Avg. Proj Created by Mo current YR = 

VAR _currentYear = YEAR(TODAY())
VAR _Months =
    CALCULATETABLE(
        VALUES('Date'[Year Month]),
        FILTER('Date', YEAR('Date'[Date]) = _currentYear)
    )

VAR _result =
    CALCULATE(
        AVERAGEX(
            _Months,
            [Projects Created]
        ),
        USERELATIONSHIP('Date'[Date], 'Projects'[Created Date])
    )

RETURN
    _result

Nested Measure [Projects Created]:

Projects Created = 
CALCULATE(
    COUNTROWS('Projects'),
    USERELATIONSHIP('Date'[Date], 'Projects'[Created Date])
)

[v-jianpeng-msft · Accepted solution]

Hi, @rpiboy_1 

To avoid nested CALCULATE and simplify the measure, we can:
Use a single CALCULATE with USERELATIONSHIP to activate the inactive relationship.
Combine the logic of Avg. Proj Created by Mo current YR and Projects Created into a single measure.

Proj Created by Mo current YR (Optimized) = 
VAR _currentYear = YEAR(TODAY())
VAR _Months =
    CALCULATETABLE(
        VALUES('Date'[Year Month]),
        FILTER('Date', YEAR('Date'[Date]) = _currentYear)
    )
RETURN
    CALCULATE(
        AVERAGEX(
            _Months,
            COUNTROWS('Projects')
        ),
        USERELATIONSHIP('Date'[Date], 'Projects'[Created Date])
    )

Best Regards

Jianpeng Li

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

[sjoerdvn]

I think the nested measure is mostly OK, but I does seem like the USERELATIONSHIP  in your main measure is redundant. I would rewrite the main measure to a single expression (no need for VARs and RETURN).

[rpiboy_1]

I get that the VARs and RETURN are likely uncessary, but my understanding is that should not impose any unccessary overhead, and at least for me it makes it easier to troubleshoot and incrementally build the measure in the first place. That said, when I tried without the USERELATIONSHIP in the Main measure it fails. In both case the calculate is being used to impose the alternate relationship path to be used to resolve the calculations, perhaps it is what it is...? I tried collapsing the [Projects Created] measure into the Main Measure and still ultimatly ran into issues. I was not able to get it down to a single CALCULATE/USRERELATIONSHIP wrapper.