Forum Discussion

JoannaSK's avatar
JoannaSK
Microsoft Employee
6 months ago
Solved

Calculated column with max id in group for DirectQuery

I have a DirectQueryconnection to a cube with two tables, rpt_porequisition and rpt_requisitionactivity.

The model claims they have a 1-to-1 bidirectional relationship on RequisitionID, which seems unlikely since the unique id in rpt_requisitionactivity is RequisitionActivityID, and each RequisitionID appears multiple times in that table.

Since this is a DirectQuery connection, I am unable to create or alter relationships between cube tables.

 

I would like to create a table visual that shows data from rpt_porequisition, plus data from rpt_requisitionactivity associated with the max RequisitionActivityID for each RequisitionID.

 
I tried this to calculate the max activity per req:
CALCULATE
(   MAX(rpt_requisitionactivity[RequisitionActivityId]),
    ALLEXCEPT(rpt_requisitionactivity,rpt_requisitionactivity[RequisitionId])
)
 
but as a calculated measure, it fails with a "too many rows" error when I try to put it in a visual, and as a calculated column, it tells me CALCULATE is not allowed as part of a calculated column on DirectQuery models.
 
I would like to end up with an "IsLatestActivityForReqID" field that returns 1/0 or true/false for each rpt_requisitionactivity row, that I can use to filter the visual. What is the best way to accomplish this?
Need Help

8 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    6 months ago

    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:

    LinkedIn

     

    • JoannaSK's avatar
      JoannaSK
      Microsoft Employee
      6 months ago

      Are these measures or columns? Does it matter which table I create them in?

  • danextian's avatar
    danextian
    Super User
    6 months ago

    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.

    • JoannaSK's avatar
      JoannaSK
      Microsoft Employee
      6 months ago

      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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 months ago

    Hi,

    Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    6 months ago

    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 Learn

    We 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.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    6 months ago

    Hi JoannaSK,

    We would like to follow up and see whether the details we shared have resolved your problem. If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

    Thank you.

  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    Community Support
    6 months ago

    Hi JoannaSK,

    We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

    Thank you.