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

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
Icon for Community Support rankCommunity 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
    Icon for Community Support rankCommunity 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
      Icon for Community Support rankCommunity 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

      • v-achippa's avatar
        v-achippa
        Icon for Community Support rankCommunity 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.
        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