Calculated table DAX overwrites calculated column code

[TM_Visual]

In my PBI report I have a calculated table (written in DAX). There is a complex calculated column added to the table.

On two occasions, I have edited the DAX in that calculated column, and after completing it, found that the DAX has been overwritten by the code for the entire table.

The first time I thought I must have mis-copied the DAX, but the second time I was certain that I had not.

Because there is no undo function that affects it, this has lost me hours of work.

This was using the March release.

[TM_Visual]

UPDATE: within 20 minutes of posting the above, I suffered the second issue mentioned: All of the code for my calculated table has been overwritten by the code for a calculated column within the table.

The entire table now shows as an error.

I believe this was prompted by moving away from the code-box for the DAX. I was in the data view.

I think that while it was loading and then trying to apply a filter to a column. I have a cameraphone photo of a data view drop-down filter box that shows only a string of a dozen  #ERROR.

[edit] Again, later the same day. This time prompted by trying to clear filters from the tables in the data view.

[v-chuncz-msft]

@TM_Visual 

The latest version (2.80.5803.1061) works fine for me. You may try it and create a support ticket for assistance if necessary.

[TM_Visual]

@v-chuncz-msft 

I have replicated a version of this issue in the latest version, 2.80.5803.1061 (20.04) (x64)

Summary

The code for a calculated table is overwritten by the code for a column within that table, when the DAX editor is switched between them in a certain way.

Context

I have a table called 'Summary FTE' calculated in DAX. 15 Columns (plus 4 further calculated), 8,800 rows.

The data brought into the calculated tables is from around 5 imported tables, with the main fact table around 800,000 rows.

The basic functions are CALCULATETABLE ( SUMMARIZE ( ...

On the table there is a calculated column, 'Predicted'. This is a complex column, which has a large number of variables using lookups to other tables calculated in DAX.

In case it matters, the basic logic is 'Multiply amount in column '2020' by variable 'rate', where 'rate' = if value in column '2019' is less than 4, lookup on Table 2, where alternate result for the lookup is lookup on Table 3, then potentially lookup on Table 4' etc.

The DAX for the column has 120 rows (including lots of comments and line breaks) and 30 variables, and outputs a decimal number.

To replicate

In the data view, I click on the calculated column 'Predicted', and click in the formula editing box as if I was going to edit the DAX. I then right click on the top bar of a field of the calculated table (as if I were trying to bring up the context menu to sort, clear filters etc).

Outcome and bug

The DAX in the calculated table is replaced by the code in the column 'Predicted'. Naturally, this breaks it. Ctrl-Z or escape will not undo the change.

Once this occurs, the only way to fix it is to copy in the code for the table (if you have a copy available). Once this is done the table will calculate and work again. However the bug is instantly replicatable.

My guess is that there is an error with how the DAX editting pane is dealing with switching from a calculated column to a calculated table, perhaps when there is a large formula.

From the session diagnostics:

Peak Virtual Memory: 39.2 GB

Private Memory: 640 MB

Peak Working Set: 757 MB

.pbix is around 330MB.

Data cache maximum is 8192MB, PC RAM is 64GB. 

[TM_Visual]

Update: I have replicated this with the latest release, May 2020: Version: 2.81.5831.761 64-bit.

It can be replicated in my report whenever there is a click in the formula box of a calculated column on a DAX table, to a header of a column in the DAX table, even on small columns.

[Alexander76877]

Hi, I was going to submit the very same bug report but since it is already here I voted for it. This error is really a pain and still occurs in the July 2020 version. 

Having Dax-calculated tables is really nice because you can speed up visuals by precalculating required values for display (similar to the "manage aggregations"). Very complex models with multiple bi-directional relations become fast, from "unusable" to "wow". 

So, please fix the bug.

Alexander 

[e2matt]

I'm seeing something odd as well, it's not exactly the same but it might be related.

When I add a calculation table with YTD, MoM, YoY, etc, every number field, no matter the type loses the sigma icon on the field explorer and won't work in the calculation table. To get it working I need to create a measure to do a sum on that field then use the new sum field.

It's almost like adding a calculation field changes the properties of the entire model...