Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
martinrowe
Advocate I
Advocate I

Analyse Calculated Column Performance

As per the title, is there a standard way to analyse the performance of calculated columns in the power bi desktop model? I guess similar to dax studio and measures.

 

I am in a situation where the reports/visuals themselves are satisfactory, however the initialising of the model, adding/editing columns or measures in the desktop application is becoming very slow. For example to add a new measure the file displays "working on it" for approximately 5 minutes before allowing the statement to be added, then after writing the statement a further 5 minutes of "working on it" to load this into the model. The report is also unable to be refreshed on a device with 16GB RAM or less.

 

The model is approximately 3x tables with 2mn rows, 1x 2.5mn rows with some further reference data tables (x4) between 50 and 3,000 rows each.

 

Im pretty sure that the issue is with existing calculated columns in the model, in particular ones where I have to do lookupvalue on some of the reference data, so I would like a way to analyse and understand which ones are using the most resources and taking the longest so I can focus on optimising them.

5 REPLIES 5
Anonymous
Not applicable

I have the same issue.

I m trying to find the best option between 2 different ways of calculating a column.
bUT i DID NOT FIND ANY SOLUTION (except for empiric test that is difficult in dev, and time consuming)

Help =D

1) 

SWITCH(
TRUE(),
aaa[id] IN VALUES (bbb[id]),"S",
aaa[id] IN VALUES (ccc[id]),"T",
"na"
)

2) 
VAR VLOOS=LOOKUPVALUE(bbb[ID],bbb[Iid], aaa[ID])
VAR VLOOT=LOOKUPVALUE(ccc[ID],ccc[ID], aaa[ID])
RETURN
SWITCH(
TRUE(),
NOT(ISBLANK(VLOOS)),"S",
NOT(ISBLANK(VLOOT)),"T", "NA"
)
Anonymous
Not applicable

Bump - I am also interested in this troubleshooting feature

JohanT
Frequent Visitor

Hi @martinrowe

 

I would try the Performance Analyzer. There you can see what DAX-querrys is taking the most time etc. Have you tried it?

 

https://www.sqlbi.com/articles/introducing-the-power-bi-performance-analyzer/

 

image.png

 

//J

my understanding of this tool is that this is for measures and visuals which are computed on demand and can lead to slow reports. Calculated columns are computed on refresh and stored in memory as part of the data model. My issue is not with slow visuals but this pre-computation that happens when I refresh the model or edit it.

 

Unless I have missed something on how this tool works I dont believe it will give me what I am after? However the kind of insight this tool provides would be of use, the question is how to obtain this for calculated columns that are pre-computed?

I take it nobody has a solution for this? Given it is a DAX formula I would have assumed that there would be some method somewhere to assess calculated column performance?

 

Second to the actual performance assessment which is still the main aim of the post; I believe that the problematic calcuated columns are where I generate a transaction sequence number using this calculation (this was taken from other DAX queries as the acceted solution) as this table is >2M rows. If there is an alternative way to do this then that may be a good starting point

 

Transaction Sequence Number (All Sales) =
VAR CurrentRow = Transaction_History[Transaction_History_ID]
RETURN
IF(Transaction_History[Transaction_Type]="sale",
   COUNTROWS (
      FILTER (
           CALCULATETABLE (
                   Transaction_History,
                           ALLEXCEPT ( Transaction_History, Transaction_History[Subscription_ID] )
                          ),
               Transaction_History[Transaction_Type]="sale" &&
               Transaction_History[Transaction_History_ID] <= CurrentRow
          )
    ), BLANK()
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.