What is the effect of the data source on a table visual loading?

[justlearning50]

Hello,

I have an odd issue I hope someone can shed light on.

I have the same data being read into a power BI dataset. Source 1 is multiple excel files which are merged in PBI, source 2 is a SP online list. Both sets of data load in a reasonable time frame. However when displaying the data from the list in a table visual, it times out and with the error 'The visual has exceeded the available resources'. Displaying the same data from the merged excel files is instantaneous.  Can anyone let me know why this would be the case, please? 

Thanks all.

[amitchandak · Accepted solution]

@justlearning50 , is only display causing it or measure causing it? Please check the measures you are using, they might have performance issues. In case there is cumulative measure try to use window function or use visual level calculation

[justlearning50]

Hi @amitchandak, yes it seems to be the measure that is causing this issue. Thank you. I thought there was an error in it, so I've edited it slightly. However it now gives the total number in my column (but is quick) for each row rather than counting where the entry is 'Skill Achieved' in my 'Result' column. (Same result for each row)

This is my measure and it works fine where excel is the data source. (column names are slightly different of course)

Total skill numbers =
var selectedSite = SELECTEDVALUE('ListName'[Group]) //results from a slicer selection
RETURN
CALCULATE
(
COUNT
(
ResultsTable[Achieved skill]
),

FILTER
(
ResultsTable,
ResultsTable[Achieved skill]= "Skill Achieved" &&
CONTAINSSTRING
(
ResultsTable[GroupName], selectedSite
)
)
)

Example below of what I have/hope to achieve. My measure may not be correct, but seems to work fine for the excel data.. (I hope this makes sense)

Skill Name	GroupName	Result
Skill 1	Group A	Skill Achieved
Skill 2	Group A	Skill Achieved
Skill 3	Group A	Skill Achieved
Skill 1	Group A	Skill Achieved
Skill 2	Group A	Skill Gap
Skill 3	Group A	Skill Achieved
Skill 7	Group A	Skill Achieved
Skill 1	GroupB	Skill Achieved
Skill 2	GroupB	Skill Achieved
Skill 3	GroupB	Skill Achieved
Skill 1	GroupB	Skill Gap
Skill 2	GroupB	Skill Gap
Skill 3	GroupB	Skill Achieved
Skill 7	GroupB	Skill Achieved
If Group A is selected from a slicer I should get the results below
Skill 1	2
Skill 2	1
Skill 3	2
Skill 7	1