calculation group in direct query sap hana

[DiKi-I]

Hi,

I'm pulling data from sap hana using direct query. I have to create various calculation like ytd, last 3 months etc and use them as slicer in the report for multiple measures. In direct query I'm not getting the option to create calculation group. Could someone please help with the best way to create this calcuation?

[v-pnaroju-msft]

Hi DiKi-I,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft]

Hi DiKi-I,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Hi DiKi-I,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thank you, @DataNinja777 ,@Ritaf1983 , @danextian , and @rohit1991 , for your responses.

Hi @DiKi-I,

We appreciate your query posted on the Microsoft Fabric Community Forum.

In addition to the responses provided by @DataNinja777 , @Ritaf1983, @danextian and @rohit1991, we kindly request you to refer to the following links, which may help in resolving the issue:
DirectQuery for SAP HANA in Power BI - Power BI | Microsoft Learn
DirectQuery in Power BI - Power BI | Microsoft Learn

If you find our response helpful, we request you to mark it as the accepted solution and provide kudos. This will assist other community members who may have similar queries.

Thank you.

[rohit1991]

Hi @DiKi-I ,

When using DirectQuery with SAP HANA in Power BI, it's true that certain features like calculation groups (usually created in Tabular Editor for Import models) aren't supported. To implement time-based calculations like YTD, last 3 months, etc., your best approach is to create these as separate DAX measures manually.

Then, to allow users to select among them (like in a slicer), you can use a disconnected table containing labels such as "YTD", "Last 3 Months", "MTD", etc. and then use a switch measure that dynamically returns the appropriate calculation based on slicer selection. This method mimics the behavior of a calculation group without needing to actually define one. Keep in mind that working with DirectQuery has performance limitations, so it’s best to optimize the model and restrict the data volume where possible to ensure responsiveness.

[danextian]

Aside from what @Ritaf1983  and @DataNinja777 has mentioned, does it have to be direct query? I understand that you might want a near real-time data but DQ imposes several limitations. Perhaps a composite model will work.

[DiKi-I]

Yes as per their licence they cannot take data out of sap so this needs to be in direct query mode.

[Ritaf1983]

Hi @DiKi-I 

Calculated Groups are not supported in DirectQuery mode in Power BI, including when connecting to SAP HANA. Here are key limitations relevant to this scenario:

No Calculation Groups in DirectQuery: DirectQuery mode does not allow the creation of calculation groups, which are typically used for time intelligence calculations like Year-to-Date (YTD) or Last 3 Months. This limitation arises because DirectQuery relies on sending queries directly to the data source without performing complex transformations or aggregations in Power BI itself.

Limited Transformations: DirectQuery mode restricts the types of transformations and calculations that can be performed. For example, calculated columns are limited to intra-row operations and cannot use aggregate functions. Similarly, parent-child DAX functions and clustering capabilities are unavailable.

SAP HANA-Specific Constraints: When using SAP HANA as a DirectQuery source, treating it as a multi-dimensional source (default behavior) prevents adding calculated columns or customizations in the semantic model. This restriction means that measures like YTD or Last 3 Months must be predefined in the SAP HANA calculation view rather than created dynamically in Power BI.

To address these limitations, you can:

Create the required time intelligence measures (e.g., YTD, Last 3 Months) directly within SAP HANA's calculation views.

Consider switching to Import mode if feasible, as it allows greater flexibility for creating calculation groups and performing advanced transformations within Power BI.

These constraints highlight the trade-offs of using DirectQuery for real-time data access versus Import mode for advanced modeling capabilities.

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

[DataNinja777]

Hi @DiKi-I ,

In DirectQuery mode with SAP HANA, Calculation Groups are not supported, which makes it challenging to dynamically apply time-based calculations like Year-to-Date (YTD) or Last 3 Months across multiple measures. A workaround is to manually create a disconnected parameter table using “Enter Data” in Power BI. This table might look like:

Time View	Time Filter Index
Current Period	1
YTD	2
Last 3 Months	3
Last 12 Months	4

Name this table 'TimeSelector' and use the 'Time View' column in a slicer. Then, create a measure to capture the selected filter:

SelectedTimeFilter = SELECTEDVALUE('TimeSelector'[Time Filter Index])

For each base measure (e.g., Sales), create a dynamic measure using SWITCH and CALCULATE to apply the appropriate date filter logic:

Dynamic Sales Measure :=
VAR SelectedFilter = SELECTEDVALUE('TimeSelector'[Time Filter Index])
RETURN
SWITCH(
    TRUE(),
    SelectedFilter = 1, [Sales],
    SelectedFilter = 2, CALCULATE([Sales], DATESYTD('Calendar'[Date])),
    SelectedFilter = 3, CALCULATE([Sales], DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -3, MONTH)),
    SelectedFilter = 4, CALCULATE([Sales], DATESINPERIOD('Calendar'[Date], MAX('Calendar'[Date]), -12, MONTH)),
    [Sales]
)

This method simulates the behavior of a calculation group by dynamically changing the measure logic based on the slicer selection. You will need to repeat this SWITCH logic for each measure that requires time-based transformation.

If your model allows it, you can also consider using a composite model where the Calendar table is imported and only SAP HANA data remains in DirectQuery mode. This provides more flexibility for date logic without fully abandoning DirectQuery. However, performance should be closely monitored, as complex SWITCH statements in DirectQuery can slow down report rendering.

Best regards,