Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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/
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.
You say “all I need now....” but I don’t think this is what you need at all. What are you wanting to achieve?
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
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.
@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
@MattAllington Hi Matt,
Do you have any suggestions for me to implement this kind of report, please?
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 🙂
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.
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.
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
69 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
68 |