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.
Hello JoannaSK
Please try this
Create a measure that flags the latest activity per RequisitionID.
Max Activity ID per Requisition
MaxActivityID per Req =
CALCULATE(
MAX(rpt_requisitionactivity[RequisitionActivityID]),
ALLEXCEPT(
rpt_requisitionactivity,
rpt_requisitionactivity[RequisitionID]
)
)
Step 2 Is Latest Flag Measure
IsLatestActivity =
VAR MaxID =
CALCULATE(
MAX(rpt_requisitionactivity[RequisitionActivityID]),
ALLEXCEPT(
rpt_requisitionactivity,
rpt_requisitionactivity[RequisitionID]
)
)
RETURN
IF(
MAX(rpt_requisitionactivity[RequisitionActivityID]) = MaxID,
1,
0
)
Use in Visual
Add fields from both tables into your table visual
Drag IsLatestActivity into Visual Filters
Filter where IsLatestActivity = 1
Now only the latest activity row per requisition shows.
If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.
Thanks,
Connect with me on:
Are these measures or columns? Does it matter which table I create them in?