Creating Table from “DirectQuery to AS” tables.

[Taurus1796]

Hi,

I’m currently working on a Power BI project and am facing a challenge with data modeling using “DirectQuery to AS” (Azure Analysis Services). Specifically, I need to create a table that pulls fields exclusively from tables sourced through DirectQuery to AS. However, I’m encountering issues in setting this up, as it seems some fields aren’t compatible or accessible as expected in DirectQuery mode.

Could anyone guide me on how to:

1. Properly reference or combine fields only from “DirectQuery to AS” tables in Power BI.

2. Work around any limitations with relationships or calculated columns/measures that might arise in DirectQuery mode.

3. Apply transformations or filtering techniques that are optimized for DirectQuery, especially when dealing with Analysis Services.

Tried so far:

• Calculate(SelectcColumns( method

It created a table but the result exceeded 1M rows so the it maxes out and didnt return anything.

I have tried restricting the data with date filters for certain period (if you can help with DAX on best practice for this?? ) but still not worked.

I’d greatly appreciate any insights, tips, or examples on structuring this table effectively. Thank you in advance for your help!

[FarhanJeelani]

Working with DirectQuery to Azure Analysis Services (AS) in Power BI does come with some limitations, especially for complex models or large datasets. Here are some strategies that may help you work within these constraints:

1. Properly Reference or Combine Fields in DirectQuery

• Use Aggregations in AS: Since DirectQuery has a row limit and cannot materialize intermediate tables with more than 1 million rows, consider creating aggregations or pre-calculated tables in Azure Analysis Services. This reduces the data size and allows you to bring in only the required fields.
• Avoid Virtual Tables for Large Datasets: Instead of trying to create virtual tables using SELECTCOLUMNS, try to restrict data at the source level or through existing tables in AS.
• Parameterized Queries: If you're repeatedly filtering on date or another key field, parameterizing your query can help pull in only relevant subsets of data, making it easier to manage in Power BI.

2. Work Around Limitations in Relationships and Calculated Columns/Measures

• Use Only Relationships Supported in DirectQuery: DirectQuery has specific requirements for relationships (e.g., only one-to-many or many-to-one relationships with a single direction). Make sure the relationships align with these constraints.
• Calculated Columns and Measures: Calculated columns aren’t supported in DirectQuery mode, so try using measures wherever possible. Measures are evaluated at runtime, while calculated columns require row-by-row processing, which DirectQuery doesn’t handle well.
• Composite Models: Power BI now supports composite models that allow combining DirectQuery with import data. If feasible, import only the most relevant data from AS for calculated columns and other transformations, then link it to the DirectQuery tables.

3. Optimized Transformations and Filtering Techniques for DirectQuery

• Filter Early: Apply filters as early as possible to avoid pulling unnecessary data. For example, filter by date or region directly within the AS model or through Power BI’s filter pane.
• Use Simple Filters in DAX: Instead of complex DAX functions, use FILTER or CALCULATE with simple conditions to limit the dataset. For instance, to limit by date, you could use:
  DAX

  CALCULATE(SUM('Table'[Value]), 'Table'[Date] >= DATE(2023, 1, 1), 'Table'[Date] <= DATE(2023, 12, 31))
• Leverage Query Folding: Power BI’s query folding in DirectQuery mode is crucial. Try applying transformations that can be “pushed down” to the AS level rather than calculated in Power BI. This way, the processing happens on the AS server, making it more efficient.

Best Practices for Date Filtering in DirectQuery with Large Data

If date filtering is a challenge due to the size of your data, you might want to create a range that’s smaller and more manageable, either through:

• Dynamic Date Filtering: Limit data to a recent time range dynamically by using TODAY or NOW in your DAX, for example:
  DAX

  CALCULATE(SUM('Table'[Value]), 'Table'[Date] >= DATEADD(TODAY(), -365, DAY))
• Parameter-Based Filtering: Use Power BI’s parameters to define a date range at the report level. This allows users to adjust the range without hardcoding a DAX filter, which might help avoid hitting the row limit by reducing data size dynamically.

Let me know if these approaches address your challenges, or if you need specific DAX code or guidance on a particular part of the model.

[lbendlin]

It created a table but the result exceeded 1M rows so the it maxes out and didnt return anything

How many more rows does your data have?  There is a capacity setting that theoretically allows to raise the 1M limit, but the question is - to what level?  

How long does it take for the query to return with the error message?  I.e. - how fast is your AAS getting to 1M ?

Note:  The same issue appears when you access semantic models.