Transform calculated columns to calculated measures to avoid out of memory errors

[Anonymous]

Hi, 

I've a pbix file with six tables and some of them have several calculated columns. Now, I'm facing out of memory errors that prevent me from loading my data successfully. I've read that this could be related to the number of calculated columns that my model has, and therefore I'd like to know if there's a way to transform a calculated column into a calculated measure to avoid this error. 

For example, I have this calculated column: 

Merged_until_date =

VAR Updation_date=gitlab_merge_requests_bitacora[creation_date]

VAR Merged_carga_inicial = CALCULATE (DISTINCTCOUNT(gitlab_merge_requests_bitacora[merge_request_unique_identifier]),(FILTER(gitlab_merge_requests_bitacora, gitlab_merge_requests_bitacora[status] ="merged" && gitlab_merge_requests_bitacora[initial_load]="true" && gitlab_merge_requests_bitacora[creation_date]<=Updation_date)))

VAR Merged= CALCULATE(DISTINCTCOUNT(gitlab_merge_requests_bitacora[merge_request_unique_identifier]),(FILTER(gitlab_merge_requests_bitacora, (gitlab_merge_requests_bitacora[action] ="merge") && gitlab_merge_requests_bitacora[creation_date]<=Updation_date)))

RETURN Merged_carga_inicial+Merged+0

The objective of this calculated column is counting the number of distinct elements that meet certain conditions (status "merged", initial_load "true", etc.) whose creation date is previous to the one of the row that is being evaluated, adding this figure to other elements that meet certain conditions (action "merge") whose creation is, again, previous to the one of the row that is evaluated and, finally, add this two figures (RETURN clause). 

Is there a way to write this as a calculated measure? In the same way, which other actions could I execute to avoid this out of memory errors?

Many thanks for your help. Regards

[TeigeGao]

Hi @Anonymous ,

Generally, almost all calculated column can be changed into measure, but how do we write the measure depends on how do you want to display these data. For instance, I have a calculatedd column like column A - column B, then if we want to display it in a table visual with a measure, we can write the measure as MIN(column A) - MIN(column B).

Too many calculated column will increase the usage of memory, but there are many other considerations, here is a blog which discusses about this problem, please refer to it: https://www.linkedin.com/pulse/ten-techniques-optimising-memory-usage-microsoft-power-dejan-pajk

Best Regards,

Teige

[Anonymous]

Hi @TeigeGao , thanks for your answer. I have a doubt. Let's suppose your case, where I have two rows with the following values for Column A and Column B: 

Column A.      Column B.        

3                     2                      

5                     1

If I use your formula: MIN (Column A) - MIN (ColumnB), I will get the same value for the two rows, won't I? Given that the minimum value for Column A is 3, and the minimum value for Column B is 1, for both rows the value obtained would be 3-1=2, whereas the value I'd like to get is 3-2=1 for the first row and 5-1=4 for the second row. Am I wrong?

Regards!

[TeigeGao]

Which visual did you use? If it is a table visual, we can use the following one:

Measure = CALCULATE(MIN(Table1[Column1]) - MIN(Table1[Column2]))

[Anonymous]

Hi @TeigeGao . I'll simplify the situation to make it easier to find a solution .

My problem is this: I need to take the time evolution of some metrics, to display them in a line chart. I've achieved this using calculated columns, as you can see in the following image: 

But the problem is that this process takes a lot of time to generate this charts and finally PowerBI shows me a message indicating that I'm out of memory. What I'd like to get is a calculated measure that can generate a similar graph. For example, I'll show this table with the expected results I'd like to get

Merge_request_id.      Merge_status.        Updation_date         Number_closed        Number_opened

1                                 Opened                  02/02/2019              0                               1

2                                 Opened                  03/02/2019              0                               2

3                                 Closed                    04/02/2019              1                               2

4                                 Opened                  05/02/2019              1                               3

Number_closed and number_opened show cumulative values over the time (given by updation_date) of opened and closed merge requests. So what I'd like to get for each row is the number of opened or closed merge requests until the updation_date in each row. Is there a way to transform number_closed and number_opened into measures that show the temporal evolution of opened and closed merge requests?

Many thanks for your help 🙂

[TeigeGao]

Hi @Anonymous ,

Of course we can, I write the query using your sample data, if it can't meet your requirement, please feel free to tell me:

Number_closed = var a= CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1), Table1[Updation_date] <= MIN(Table1[Updation_date]) && Table1[Merge_status] = "Closed"))
return IF(a = BLANK(),0,a)

Number_opened = var a= CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1), Table1[Updation_date] <= MIN(Table1[Updation_date]) && Table1[Merge_status] = "Opened"))
return IF(a = BLANK(),0,a)

The result will like below:

Best Regards,

Teige

[Anonymous]

Hi @TeigeGao 

Your solution works great in a line chart, as you can see in the image below (thank you very much!), but it's failing with a card view, as it shows 0 when the real value is 98 :(. Is there any solution here to avoid this annoying zero?

Regards!

[Anonymous]

Hi @TeigeGao . Any idea about my issue? 😞

Regards!

[TeigeGao]

Hi @Anonymous ,

This measure cannot work in a card visual, because the card visual can't provide a visual level filter, we will need to write another measure and create some filter for it.

Best Regards,

Teige

[Anonymous]

But, in this case, @TeigeGao ,the measure in the card won't update when pressing on any point of the line chart. Am I wrong?

[TeigeGao]

HI @Anonymous ,

No, we can also use the allselected() function to get the value you selected.

Best Regards,

Teige

[Anonymous]

Hi @TeigeGao . That would be a great solution. Could you give me an example to implement it to make this two visuals be connected?

[TeigeGao]

It's pleasant if you can share the pbix file with sample data.