Add the calculated column as custom column into the table in Query Editor

[vishnuprashanth]

I have created a calculated column in my data model. I want to create or add this calculated column to a new custom column in the Edit Query Table. 

Max Value_Week = 
CALCULATE ( MAX ( Table[Value] ), ALLEXCEPT ( Table, Table[Person], Table[WeekSequence] ) )

Is it possible to copy the calculated column to the table in Query Editor? or, can anyone suggest me how to convert this DAX function to M query to add the new column to the table, please? 

TIA

[MattAllington]

You can’t access calculated columns from the query editor. Also, chances are the calculated column is not the best approach anyway (I can’t be sure because I don’t know what it is, but based on experience there may be a better way). What is in the calc column?

You may be interested in my article here https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

[vishnuprashanth]

Hi @MattAllington, Thanks for your response. 

The calculated column has the maw_value for each week/ person. Please find the attached screenshot below.

My dataset has only 3 columns - Person, Value and Weeks. I need to create a custom column with Max Value(calculated using the DAX formula mentioned above) to my table in the Query Editor. 

[MattAllington]

You say “all I need now....” but I don’t think this is what you need at all. What are you wanting to achieve?

[vishnuprashanth]

@MattAllington

I have created a calculated column in my data model - Max Value_Week

Calculated column is Max Value_Week

Now, I want to create a custom column in my table in the query editor and want to fill in the max_value_week values, either by writing an M query. 

add Max Value_Week to this table and calculate its values using the M Query

[MattAllington]

I understand what you are saying, but my advice to you is that this is likely to be the wrong way to solve your problem.  Can you put aside this for a moment and take a step back.  Why do you want to do what you say?  What are you planning to do with this once it is done?  There is likely a better way.  Please describe the end state.

[vishnuprashanth]

@MattAllington Apologies for not being clear. 

I will try and explain my problem and the output I am expecting in detail.  I have the below-attached dataset.Actual Dataset

Then I created calculated columns(marked below) for the variables. 

calculated coulmns

I unpivoted the columns to have them in the same columns as attributes. 

unpivoted columns

I needed to create a graph as shown below. .. But since I have unpivoted the columns, my calculated columns are throwing me an error as """Column 'max P ' in table 'PT2' cannot be found or may not be used in this expression."""

so, I thought of creating those columns in the table directly to create a bar chart like this.. 

Create bar chart for comparing the attributes

[vishnuprashanth]

@MattAllington Hi Matt,

Do you have any suggestions for me to implement this kind of report, please?

[vishnuprashanth]

Just got an Idea Matt. Let me try it tomorrow and see if that is working. Thanks a lot for your response. I will try that and let you know if I managed to solve the problem. 

Thanks again 🙂 

[MarkS]

Hi @vishnuprashanth,

I am not sure that I completely follow what you are after, but could you not just add the field a second time to the values of the visualization and change the aggregation to Maximum.  As shown below where I added a field named hours to a chart twice, once as the sum and a second as the Maximum.

[vishnuprashanth]

Hi @MarkS

I have already tried it. If I do like that, the labels are shown as Legends and are not displayed in the X-Axis.  want the labels to be displayed on the X-Axis.