Forum Discussion
Power BI table load
hi, i have a table called glentry that i am getting from a warehouse in fabric to power bi in directquery mode. my table has alot of columns, mainly the ones i am facing a problem with are the columns source no and source name... focusing on source name i'd say is the bigger problem. when i add these columns together with a few other columns, they work fine. but if i add them with even 1 measure, the table takes for ever to load, and maybe even doesnt load. note that i have well around 30m+ rows.they load completely fine until i add 1 measure. if i remove only the source name, and i add all my 7 measures, the report works fine. if i add the source name, the report stops loading.
here is a measure that i am using that is the same for all other 6 measures except each has its own column in the table:
Balance =
VAR max_date = MAX(DateTable[Date])
VAR balance =
CALCULATE(
SUM(GLEntryView[Balance]),
ALL(DateTable),
DateTable[Date] <= max_date
)
RETURN
balance
note that, if i add the columns being used for each measure (balance, balance acy, credit, debit, etc...) they load perfectly fine even with the source name being there
NOTES:
the source no/name are in the same table as the glentry,
the source name is not a calculated column, it is being pulled directly from the source,
i dont have filters or slicers applied, but when i apply them while the issue is there, like for example i choose 1 brand from my brands slicer, the table loads,
i am not using aggregations or grouping,
It sounds like [source name] is a high granularity field. You are likely creating a large data cache to answer the query. I would capture the query plan with DAX studio and look at the query being issued to the warehouse, and how much processing is occuring in Power BI. Ideally you'd push as much back to the warehouse as possible, this might require rewriting the DAX to force that behaviour.
You say you are not using aggregrations. If the [Soruce Name] is not too high cardinatility you could think about a import or DirectQuery aggregation to speed up the query.
4 Replies
It sounds like [source name] is a high granularity field. You are likely creating a large data cache to answer the query. I would capture the query plan with DAX studio and look at the query being issued to the warehouse, and how much processing is occuring in Power BI. Ideally you'd push as much back to the warehouse as possible, this might require rewriting the DAX to force that behaviour.
You say you are not using aggregrations. If the [Soruce Name] is not too high cardinatility you could think about a import or DirectQuery aggregation to speed up the query.
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi Hussein_charif
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.
Thanks
