help with DAX (replace calculated columns with Measures)

[nexami]

- dashboard : this is the main tab that I would like to use, to be updating automatically when new raw data is available.

- raw data :

a) columns A-U show the real raw data. I would like this raw data to be inserted in DAX as a link to an external file; so that whenever the "real raw data.xls" file is replaced by a new version; all the "computed raw data" updates; as well as the dashboard pivot graphs and tables updates automatically.

b) columns V-AG show the computed raw data. I need these columns for the pivot graphs and tables located on the dashboard. I have tried to use the same excel formulas in the PowerPivot DAX table but they didn't work ; hence the reason why I am seeking your help here to create "measures" to have these columns computed in DAX?

- product_families / zip / price : these 3 tabs have some information used for the calculations made in columns V-AG inside the "raw data" sheet.

My goal is to : - have the pivot graphs /tables update effortlessly just by having replaced the external "real raw data" file; using DAX in order to have responsiveness to my charts.

PS: i've no idea how to attach my XLSB sheet that has all the working as mentioned in above points.

[v-rzhou-msft · Accepted solution]

Hi @nexami 

I think you want columns V-AG which show the computed raw data update automaticlly with the updating of your real data (A-U). I think custom column(M query in Power Query Editor), calculated column or measure can achieve your goal. All of them are created based on your real data, so all of them will update with the refresh of your data.

You can create them by your requirement.

How to and Differences:

1. Custom columns can be created in Power Query Editor by M query. This will be stored as underlying data and will use memory. For reference: Add a custom column in Power BI Desktop

2. Calculated column can be created in report view by dax. It will use your memory. You can find your calculated column in your data view. But you couldn't find your calculated column in Power Query Editor.

3. Measure can be created in report view by dax. Measure is aggregate value, so you couldn't find it in data view. It will use your CPU. To transform a calculate column, you need to use aggregation dax function like sum, max.

EX:

Calculate column is like

Calculated column = calculate(Table[A],Filter(Table,...))

To transform it to Measure, you need to use sum to number type and max/min to text and other types.

Measure = calculate(Sum/Max(Table[A]),Filter(Table,...))

For reference: Calculated Columns vs Measures

Best Regards,

Rico Zhou

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