Optimise a SELECTEDVALUE formula

[Raul]

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	EAN	UxB	Amount
cat1				4.250
Item 1	A7500	4358006320	10	2.500
Item 2	B2500	7458820030	12	1.750
cat2				5.000
Item 3	D300	50003000	2	3.000
Item 4	F200	50006850	5	2.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!

[v-xiaotang]

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]

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.

[v-xiaotang]

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.

[amitchandak]

@Raul , try if this can improve it a bit

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

[Raul]

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