The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.

**Save €200 with code MSCUST on top of early bird pricing!**

- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Calculation group Nesting of measures

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Calculation group Nesting of measures

06-26-2023
10:50 AM

Hello All,

I understand we can't do nesting of calculation items as it would result in sideways recursion and this is not recommended. However, I am confused about nesting of measures in a calculation item and how that would affect the results. If we have a calculation item that references a measure which in turn references other measures, is it supposed to work fine?

As an example, consider I am calculating a price variances using a dax code simliar to this. This is for MTD Price Variance. If I execute a calculation item for YTD, somehow the result generated is the correct one.

As you can see below, price variance is based on other variables (like CY Price, PY Price and PY Sales) and I am very surprised that calculation grouping is working despite these nested measures. Is this just by luck or is the calculation group meant to acheive this kind of behavior?

I thought in this article by SQL BI it said it is not possible to propogate measure references used in nested calculation and perhaps I am not understanding the context in which that comment was made. Thanks

*VAR CY Price = **[CY Sales]/[QTY]*

*VAR PY Sales = **CALCULATE ([CY Sales], SAMEPERIODLASTYEAR ('Date'[Date])*

*VAR PY Price = **[PY Sales]/[PY Qty]*

*VAR Price Variance = **([CY Price]-[PY Price])*[QTY]*

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-28-2023
04:18 PM

Hello @daxreport

I think the part of the SQLBI article you're referring to is:

*Because the [measure reference] replacement takes place only at the report level where the calculation groups are usually applied, it is not possible to propagate the replacement to measure references used in nested calculations. This behavior limits your ability to apply calculation groups only to a specific subexpression of a complex DAX calculation.*

My short explanation of this would be:

- When Calculation Groups are used in the "usual" way, that is by adding a measure to a visual in a report and applying a filter on a particular Calculation Item, the measure used in the report is in its entirety replaced by the Calculation Item.
- However, if you want to restrict the Calculation Item to apply
*just*to a sub-measure or sub-expression within the definition of the measure used in the report, this cannot be done when Calculation Groups are used in the usual way. - In order to solve this issue, the author of the article shows how the model can be modified and the Calculation Item thena applied with DAX so that it impacts just the sub-measure.

To explain why you are actually getting the result you expect with something similar to your **Price Variance** example:

Assume the **Price Variance** measure is defined as:

```
Price Variance =
VAR CY_Price = [CY Sales] / [QTY]
VAR PY_Sales =
CALCULATE ( [CY Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR PY_Price = [PY Sales] / [PY Qty]
VAR Price_Variance = ( CY_Price - PY_Price ) * [QTY]
RETURN
Price_Variance
```

Then assume we have a Calculation Group with a "YTD" Calculation Item:

```
CALCULATE (
SELECTEDMEASURE (),
DATESYTD ( 'Date'[Date] )
)
```

When you apply the YTD Calculation Item to Price Variance, this is what is effectively calculated:

```
Price Variance (YTD) =
CALCULATE (
VAR CY_Price = [CY Sales] / [QTY]
VAR PY_Sales =
CALCULATE ( [CY Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR PY_Price = [PY Sales] / [PY Qty]
VAR Price_Variance = ( CY_Price - PY_Price ) * [QTY]
RETURN
Price_Variance,
DATESYTD ( 'Date'[Date] )
)
```

Because the YTD Calculation Item applies a YTD filter to the original measure, all measures/expressions within the original measure inherit the new YTD filter.

You will get similar behaviour whenever a Calculation Item applies some sort of filter to SELECTEDMEASURE() via CALCULATE, and the original measure itself performs calculations that all respond intuitively to any filters applied.

Regards,

Owen

2 REPLIES 2

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-28-2023
04:18 PM

Hello @daxreport

I think the part of the SQLBI article you're referring to is:

*Because the [measure reference] replacement takes place only at the report level where the calculation groups are usually applied, it is not possible to propagate the replacement to measure references used in nested calculations. This behavior limits your ability to apply calculation groups only to a specific subexpression of a complex DAX calculation.*

My short explanation of this would be:

- When Calculation Groups are used in the "usual" way, that is by adding a measure to a visual in a report and applying a filter on a particular Calculation Item, the measure used in the report is in its entirety replaced by the Calculation Item.
- However, if you want to restrict the Calculation Item to apply
*just*to a sub-measure or sub-expression within the definition of the measure used in the report, this cannot be done when Calculation Groups are used in the usual way. - In order to solve this issue, the author of the article shows how the model can be modified and the Calculation Item thena applied with DAX so that it impacts just the sub-measure.

To explain why you are actually getting the result you expect with something similar to your **Price Variance** example:

Assume the **Price Variance** measure is defined as:

```
Price Variance =
VAR CY_Price = [CY Sales] / [QTY]
VAR PY_Sales =
CALCULATE ( [CY Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR PY_Price = [PY Sales] / [PY Qty]
VAR Price_Variance = ( CY_Price - PY_Price ) * [QTY]
RETURN
Price_Variance
```

Then assume we have a Calculation Group with a "YTD" Calculation Item:

```
CALCULATE (
SELECTEDMEASURE (),
DATESYTD ( 'Date'[Date] )
)
```

When you apply the YTD Calculation Item to Price Variance, this is what is effectively calculated:

```
Price Variance (YTD) =
CALCULATE (
VAR CY_Price = [CY Sales] / [QTY]
VAR PY_Sales =
CALCULATE ( [CY Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
VAR PY_Price = [PY Sales] / [PY Qty]
VAR Price_Variance = ( CY_Price - PY_Price ) * [QTY]
RETURN
Price_Variance,
DATESYTD ( 'Date'[Date] )
)
```

Because the YTD Calculation Item applies a YTD filter to the original measure, all measures/expressions within the original measure inherit the new YTD filter.

You will get similar behaviour whenever a Calculation Item applies some sort of filter to SELECTEDMEASURE() via CALCULATE, and the original measure itself performs calculations that all respond intuitively to any filters applied.

Regards,

Owen

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

06-29-2023
03:18 AM

Hello, @OwenAuger That is a very clear explanation. Better than SQLBI!! Thanks for your detailed response!

Announcements

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

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

Top Solution Authors

User | Count |
---|---|

47 | |

27 | |

15 | |

15 | |

14 |

Top Kudoed Authors

User | Count |
---|---|

45 | |

44 | |

41 | |

27 | |

22 |