Forum Discussion
Table Visual timeout across multiple dimensions
Hello,
I'm having issues adding multiple dimension columns into a table visual - I have built a star schema (shown in the picture), and when I add in a 2nd dimension my table times out. I'm using a series of measures that replicate across different D365 Transaction Types based on the fact tables.
Member Gross Premium =
CALCULATE (
SUM ( 'F Member Transaction Line Item'[Member Original Currency Amount] ),
CALCULATETABLE (
'D365 Transaction Type',
'D365 Transaction Type'[D365 Transaction Type Name] = "Premium"
)
) + 0
Member Gross Premium - Unpaid =
VAR Settled =
CALCULATE (
SUM ( 'Member Settlement'[Member Settled Original Amount] ),
CALCULATETABLE (
'D365 Transaction Type',
'D365 Transaction Type'[D365 Transaction Type Name] = "Premium"
)
)
RETURN
( Settled - [Member Gross Premium] ) * -1
So when I add the measures in it works fine, I add in data from the "Premium Transaction" dimension and this works fine, then If I try to add in a column from the "Currency" Dimension the table runs out of memory and I need to add dimension data from more tables.
It's a medium size model with around 20mil rows (metadata shown below) and this is built using Kimbal - I have even separated out string columns into their own dimension.
If I have 2 table visuals and select a row Power BI filters the row very quickly and gives the desired result but Ideally I need all the information in one table.
Model Meta DataStar Schemareport page
Ended up changing some of the model fct and dimensions to achieve optimal performance
4 Replies
- AccelinsFrequent Visitor
Ended up changing some of the model fct and dimensions to achieve optimal performance
- lbendlinSuper User
What made you use CALCULATETABLE rather than FILTER ?
DAX: Difference between FILTER and CALCULATETABLE - Minova
FILTER vs CALCULATETABLE: optimization using cardinality estimation - SQLBI