Forum Discussion
Calculated column with max id in group for DirectQuery
Thankyou, Ashish_Mathur, pankajnamekar25 and danextian for your responses.
Hi JoannaSK,Based on our understanding, in DirectQuery models, calculated columns that require aggregating across rows, for example, using CALCULATE or ALLEXCEPT to compute a per group MAX may not be supported. This could be the reason why creating an IsLatestActivityForReqID column fails.
The “resultset exceeded maximum rows” error may occur when you place fields from both tables into a table visual. Power BI must expand activity rows per requisition, and in DirectQuery, large intermediate rowsets can exceed engine limits.
If a physical row level “IsLatest” column inside the activity table is required, that might need to be implemented upstream in the cube or by moving to Import or composite mode, as row ranking logic is expected to be pushed to the source in DirectQuery models.
Additionally, please refer to the following links:
DirectQuery in Power BI: When to Use, Limitations, Alternatives - Power BI | Microsoft Learn
DirectQuery model guidance in Power BI Desktop - Power BI | Microsoft Learn
DirectQuery in Power BI: When to Use, Limitations, Alternatives - Power BI | Microsoft Learn
CALCULATE function (DAX) - DAX | Microsoft Learn
ALLEXCEPT function (DAX) - DAX | Microsoft LearnWe hope the information provided helps to resolve the issue. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.
Thank you.
Hi JoannaSK
There are limitations when using direct query. Some might work but with too many rows, it might now. Try the following below
test =
MAXX (
FILTER (
ALL ( rpt_requisitionactivity ),
rpt_requisitionactivity[RequisitionId]
= MAX ( rpt_requisitionactivity[RequisitionId] )
),
rpt_requisitionactivity[RequisitionActivityId]
)
If this doesnt work, limit the number of rows in the visual or create the column at the source.
When I create a measure with that code, the visual fails because the resultset exceeded the maximum number of rows.
When I create a calculated column with that code in the rpt_requisitionactivity table, it returns 101338133 for all rows - that activityid is associated with requisitionid 1200002718. The actual highest activityid is 103529804, associated with requisitionid 101569228.
I do not own the source, so I can't add a column there.