Using cubevalue/power pivots connected to semantic model
Hi everyone! I've recently began my powerbi journy coming from a background of heavy Sql and power query work in excel. As the more technical individual in my finance org I've been tasked explore and revamp our reporting through PowerBI. Due to differing data granularity I have 2 fact tables that switch between actuals and budget however I focused on keeping normalized 1:m relationships.
As finance orgs do leadership wants the same matrix report accessible in excel however I'm finding the cubevalue functions I'm creating (converted from power pivots) to be painfully slow when modified via slicer. Does anyone have recommendations on how to optimize? The underlying dax measures use switch statements and conditional logic to define certain KPIs. I've explored Dax studio and seems like the main bottleneck is large FE costs.
Within powerbi at most matrices take 1 sec to load but in excel it's up to minutes.
Are there specific limitations with this use case of trying to build reports connected to powerbi semantic model? Leadership is stuck on having the powerbi semantic model be the gold source of truth but I've built similar reports in excels data model that run way faster with data ingested directly from our database
Would love to hear if anyone else has created large cubevalue reports ine excel. Any insight is appreciated!!