Forum Discussion

Bright_Chen's avatar
Bright_Chen
Frequent Visitor
1 year ago
Solved

Data limit Issues

Recently, the charts I had previously completed have started to display error messages and throw exceptions: "When a filter condition uses the In operator with a Table argument, all of the Expression...
Need Help
  • v-achippa's avatar
    v-achippa
    1 year ago

    Hi Bright_Chen,

     

    Thank you for the response and detailed explanation. Based on your requirement please use a RANKX measure to calculate the rank of each name by total amount and then apply a visual level filter to display only the Top 5:

     

    • Create a measure for total amount, if not created already:

      TotalAmount = SUM('TableName'[Amount])

    • Now create a RANKX measure:

      NameRank = RANKX(ALLSELECTED('TableName'[Name]),

      [TotalAmount],,DESC, DENSE)

    Apply a visual level filter, in the visual’s Filters pane drag this NameRank measure and set the filter to less than or equal to 5.

    This flow is efficient, scalable and works in the new engine behaviour, no memory overhead or invalid filters.

     

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thanks and regards,

    Anjan Kumar Chippa

v-achippa's avatar
v-achippa
Community Support
1 year ago

Hi Bright_Chen,

 

Thank you for reaching out to Microsoft Fabric Community.

 

The error here is likely because of recent change in power bi’s internal DAX engine, it now enforces stricter validation when handling filters like IN or TopN that internally create table expressions.

These expressions must now contain only direct column references. If the engine detects any aliases, calculated expressions or non column projections, it throws this error even if the logic worked perfectly fine in earlier versions.

Update your filter logic to use only direct column references.

 

If you immediately need the visuals to work as before, you can download an earlier version of the desktop using Microsoft’s official link:

Previous monthly updates to Power BI Desktop and the Power BI service - Power BI | Microsoft Learn

 

But this is only a temporary workaround, future compatibility is not guaranteed and it is strongly recommended to adapt the logic as per updated behaviour.

 

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

 

Thanks and regards,

Anjan Kumar Chippa

Bright_Chen's avatar
Bright_Chen
Frequent Visitor
1 year ago
Thank you for your answer.
In that case, I would like to ask, for example, if a table has two columns, one is name and the other is amount, which is a calculated column. Then my table data may have 2,000 rows. In Power BI, I use the table visualization chart, and the same name will summarize the amount, and then the final display may have 20 rows. Then I want to display only the first five rows. For this scenario, the original approach is of course to directly set the data limit to 5.
However, due to stricter requirements, I will now attempt to use a metric value to calculate the top five names as a set, then match each column's name. If it exists in the set, the value is counted as 1; otherwise, it is 0. This metric value is then added to the visual object's filter, and selecting values of 1 will apply the filter. However, sometimes it prompts that the calculation volume is too large and exceeds the memory capacity. So, for this change, do you have a better solution to help us replace the data limit?
Thank you very much.
  • v-achippa's avatar
    v-achippa
    Community Support
    1 year ago

    Hi Bright_Chen,

     

    Thank you for the response and detailed explanation. Based on your requirement please use a RANKX measure to calculate the rank of each name by total amount and then apply a visual level filter to display only the Top 5:

     

    • Create a measure for total amount, if not created already:

      TotalAmount = SUM('TableName'[Amount])

    • Now create a RANKX measure:

      NameRank = RANKX(ALLSELECTED('TableName'[Name]),

      [TotalAmount],,DESC, DENSE)

    Apply a visual level filter, in the visual’s Filters pane drag this NameRank measure and set the filter to less than or equal to 5.

    This flow is efficient, scalable and works in the new engine behaviour, no memory overhead or invalid filters.

     

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thanks and regards,

    Anjan Kumar Chippa

    • v-achippa's avatar
      v-achippa
      Community Support
      1 year ago

      Hi Bright_Chen,

       

      As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
      If my response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

       

      Thanks and regards,

      Anjan Kumar Chippa

      • v-achippa's avatar
        v-achippa
        Community Support
        1 year ago

        Hi @Bright_Chen,

         

        We wanted to kindly follow up to check if the solution I have provided for the issue worked.
        If my response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

         

        Thanks and regards,

        Anjan Kumar Chippa