Disconnected table for measure slicer - best practise? really slow - out of memory

[jakubk]

Hi all,

i'm trying to create a ratio with a dynamic denominator

My data model is relatively small, 30k rows. The total file size is 5meg

I've created the disconnected table with my list of measures

But i can't even put the 'selected measure = selectedvalue(disconnectedtable[value])' measure onto my table/viz let alone anything that uses the dynamically selected measure

It churns for a few minutes then i get a 'out of memory' error

I have 32gb of ram (and powerbi goes up to 28-29gb before stopping) and i'm running the 64bit version of powerbi

Is there some trick to this? Does it matter which table the calculation in?

I noticed that if i include the disconnectedtable[value] column in my viz/table it seems to work. So it's like there's a cross product happening somewhere but i dont know where

[v-yuta-msft]

@jakubk ,

Actually the performance of dax formula will be affected by the data model(e.g.: relationship, columns and rows number). Generally, each dax query and visual display will generate underlying dax formula automatically, I would suggest you use Performance Analyzer to view the underlying dax formula of SELECTEDVALUE(). 

In addtion, if you need to improve the performance of your data model, I would suggest you refer to doc below:

https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jakubk]

I have about 10-15 yrs experience building data warehouses and this one is very, ery simple. Single snapshot fact table, 30k rows with 3 dims. relationships configured correctly

only the one dax calc

i found the culprit: if(hasonevalue())

But why would adding the disconnectedtable[value] to the viz make it go fast?

is hasonevalue() row by row?

i think this is the same issue - https://community.powerbi.com/t5/Desktop/Dax-Measure-Performance/td-p/266896

[v-yuta-msft · Accepted solution]

@jakubk ,

I'm not sure if hasonevalue() will iterate all related tables. You may try If(countrows(values(Table[SelectedColumn])) = 1, condition1, condition2) and check if performance has been improved.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.