Forum Discussion

Raul's avatar
Raul
Icon for Post Patron rankPost Patron
4 years ago

Optimise a SELECTEDVALUE formula

Hello community,

I have a matrix visual where for each category and item 3 columns of text type and the sales amounts are shown.

 

 Id.Item EANUxBAmount
cat1   4.250
      Item 1A75004358006320102.500
      Item 2B25007458820030121.750
cat2   5.000
     Item 3D3005000300023.000
     Item 4F2005000685052.000

 

The 3 columns of text are measures with this formula:

Id.Item = 

   IF(ISINSCOPE(Items[Item]), SELECTEDVALUE(Items[Id.Item]), BLANK())

EAN = 

   IF(ISINSCOPE(Items[Item]), SELECTEDVALUE(ItemsReferences[EAN], BLANK()), BLANK())

UxB = 

   IF(ISINSCOPE(Items[Item]), SELECTEDVALUE(Items[UxB], BLANK()), BLANK())
 
And the AMOUNT = SUM(Invoices[Amount])
 
But the visualisation performance is very poor on Desktop and in the Power BI cloud service, it shows an error of the visual has exceeded the available resources when I change the value of a customer segmentation.
How can I improve the performance of these measures?
Is there another way to do it, to present data fields in columns within a matrix?
 
Thank you!
Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Raul , try if this can improve it a bit

    Id.Item =
    IF(ISINSCOPE(Items[Item]), max(Items[Id.Item]), BLANK())

     

     

    • Raul's avatar
      Raul
      Icon for Post Patron rankPost Patron
      4 years ago

      Thanks for your reply but, in my case, the result it's the same.

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Raul 

    The error occurs when a visual has attempted to query too much data for the server to complete the result with the available resources.

    Try to replace SELECTEDVALUE() in your measures with MAX() or Min(), if problem is still not solved, try to filter the visual to reduce the amount of data in the result.

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Raul 

    Have you solved this problem? If yes, to help others find answer faster, could you kindly accept the answer helpful as the solution? Or could you kindly share your solution to improve Power BI?
    Really appreciate!
    Any question, please let me know. Looking forward to receiving your reply.


    Best Regards,
    Community Support Team _Tang

    • Raul's avatar
      Raul
      Icon for Post Patron rankPost Patron
      4 years ago

      Hi v-xiaotang and amitchandak ,

      No, the option to change the SELECTEDVALUE with MAX did not solve my problem. It does solve it if I filter the records with a filter, but it doesn't work for me. The real problem is that I have 4 columns with this problem and that's why I get the error. I had to remove them from the matrix and put their values as row data.
      Thanks anyway.