Forum Discussion
Calculated Colomn taking too much memory
HI arzukari ,
Instead of creating Calculated Column, try using them in the same Summarize function.
Also, using Calculate function in Column creates rows transitions which is not recommended.
Table2 = SUMMARIZE(Table1,Table1[PatientID],"LAst Result", LASTNONBLANK(Table1[Result],True()))Regards,
Harsh Nathani
// The easiest way to generate PatientID's is to exec: [Calculated Table] = // calculated table distinct( 'Main Table'[PatientID] ) // I understand you want to get the first result // for the patient which means the result on the // very first date recorded in the table. To get // such a result, all of them must be ordered. I assume // that only 1 result is possible for any patient // on any single date. If this is not true, you have // to have something (e.g., a time column) that would // be used to break ties. Please steer clear of // CALCULATE in calculated columns as this will // slow down calculations and will in fact bring them // to a halt for big tables. First Result = // calculated column in the 2nd table var __patientId = 'Calculated Table'[PatientID] var __result = MAXX( // Again, this will work OK only if // there are no ties for the same // patient id on the same day. TOPN(1, filter( 'Main Table', 'Main Table'[PatientID] = __patientId // I also assume that there are no // blanks in the Result column. If // there are you have to filter them // out like: // 'Main Table'[Result] <> BLANK() ), 'Main Table'[Result Date], DESC // If you have a column to break ties // you'll put it in here as well specifing // it's order. If it were a time column, // you'd write // 'Main Table'[Result Time], // DESC ), 'Main Table'[Result] ) return __result
Hi amitchandak,
So the main table (simplified) would be like this:
| PatientID | Result Date | Result |
| 1 | 8/6/2020 | Positive |
| 2 | 8/7/2020 | Negative |
| 3 | 8/8/2020 | Positive |
| 1 | 8/9/2020 | Negative |
| 2 | 8/10/2020 | Positive |
| 2 | 8/6/2020 | Positive |
| 2 | 8/7/2020 | Positive |
| 3 | 8/8/2020 | Negative |
| 5 | 8/9/2020 | Negative |
| 5 | 8/10/2020 | Negative |
| 5 | 8/6/2020 | Negative |
| 4 | 8/7/2020 | Positive |
| 4 | 8/18/2020 | Positive |
| 4 | 8/19/2020 | Negative |
| 3 | 8/20/2020 | Negative |
| 2 | 8/18/2020 | Negative |
| 5 | 8/19/2020 | Negative |
| 2 | 8/20/2020 | Negative |
| 1 | 8/24/2020 | Positive |
| 2 | 8/25/2020 | Positive |
but x18 million and exponentially increasing everyday. The above table is called Table1.
Then I create a calculated table (Table2) with the formula: SUMMARIZE (Table1, PatientID) and got the below:
| PatientID |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
I want to add column in Table2 that gets the first result from table1. So I added a calculated column:
CALCULATE (FIRSTNONBLANK(Table1[Result],1),FILTER(Table1,Table1[PatientID]=Table2[PatientID]))
This is just one requirement out of at least 50 other columns across many similar tables that I keep tweaking. The size of data is making processing the above dax formula take a lot of time and memory.
Unfortunately, the client loves exporting the underlying data of the power BI visuals, but needs each table to be summarized in a specific way.
// The easiest way to generate PatientID's is to exec:
[Calculated Table] = // calculated table
distinct( 'Main Table'[PatientID] )
// I understand you want to get the first result
// for the patient which means the result on the
// very first date recorded in the table. To get
// such a result, all of them must be ordered. I assume
// that only 1 result is possible for any patient
// on any single date. If this is not true, you have
// to have something (e.g., a time column) that would
// be used to break ties. Please steer clear of
// CALCULATE in calculated columns as this will
// slow down calculations and will in fact bring them
// to a halt for big tables.
First Result = // calculated column in the 2nd table
var __patientId = 'Calculated Table'[PatientID]
var __result =
MAXX(
// Again, this will work OK only if
// there are no ties for the same
// patient id on the same day.
TOPN(1,
filter(
'Main Table',
'Main Table'[PatientID] = __patientId
// I also assume that there are no
// blanks in the Result column. If
// there are you have to filter them
// out like:
// 'Main Table'[Result] <> BLANK()
),
'Main Table'[Result Date],
DESC
// If you have a column to break ties
// you'll put it in here as well specifing
// it's order. If it were a time column,
// you'd write
// 'Main Table'[Result Time],
// DESC
),
'Main Table'[Result]
)
return
__result
Thank you this has been very helpful.
I applied it, processing is a bit better but still slow. Is there a way to pinpoint where the inefficiencies are?