Can you explain why?

When you install the latest version of these tools (DAX Studio, Tabular Editor, Analyze in Excel for Power BI Desktop, ALM Toolkit) that tab appears automatically. Tool links available at www.sqlbi.com/tools/

Yes, a measure referenced in a calculated column will get the context transition from row context to filter context.

From a performance point of view, it's better to keep calculated columns on the same model, not on a remote model. However, oftentimes calculated columns are not required for performance and they just increase model size and slow down refresh. Do your own evaluation!

I don't understand your description, please can you try to clarify your question?

@@SQLBI Sorry for that, I was justifying the reason for seeing different values in the visual.. even though same values appear in Calculated column..

@@Bharath_PBI You're seeing different values in the visual because it's being filtered by brand. In each row, it's aggregating the grand total by the total number of rows that contain each brand. So for example, if the number 10 is repeated in the calculated column, and a product of brand X appears 9 times and a product of brand y appears 10 times and there are actually 1000 rows, in this example, brand X will show 90 (9 * 10) and brand y, 100 (10 * 10) whereas the grand total of all brands will be 10,000 (1000 rows with 10 repeated) I hope this explanation helps. I'm only a year and a half into this so I'm open to correction.

You're right, you should use calculated columns when Power Query would be too expensive - for example computing the value based on the content of other tables could be very slow in Power Query.

A measure is evaluated in the context of the cell evaluated in a report or in a DAX query, whereas a calculated column is computed at the row level within the table it belongs to

Calculated columns are computed during the database processing and then stored in the model. Measures are not stored in the model as they are computed at query time and do not use memory

If you need to operate on aggregate values you must create measures,If you need to operate on a row-by-row basis, you must create calculated columns

2. Filter context is used in measure, not calculated column.

See this: www.daxpatterns.com/events-in-progress/

@@SQLBI but the example given is only 1 fact table so might be easier to do , here I have a 2 table separately : sales orders table , and the other is the "contract table" ( which is like a campaign with start and end date , wanna see the sales during each campaign )

It really depends on many details about the report you want to produce. If there are no multiple active campaign for the same date, you should apply the active campaign to the transaction, if there could be multiple campaigns active for a single transaction you should keep the calculation at runtime. There are several discussions about similar problems in our book, even though not exactly the one you asked for: www.sqlbi.com/books/analyzing-data-with-microsoft-power-bi-and-power-pivot-for-excel/

I'm going to buy this book and learn more, don't know if it helps since the problem not quite the same as you said. Between this book and the defenitive guide to Dax ? which would u recommedn more?

The Definitive Guide to DAX is about the DAX language. Analyzing Data with Microsoft Power BI and Power Pivot for Excel is about data modeling. They cover different topics, you need knowledge on both areas. If you already know dimensional modeling (Kimball's methodology) then you only need DAX, but the question you raised is a typical data modeling question!

It depends! But a calc column is necessary only when you use the result as a groupby or filter column.

You can apply a filter on a visual based on a measure only in the filter pane, but you cannot group by a measure.

@@SQLBI Many thanks for the clarification

The first two paragraphs are certainly correct. I don't completely understand the meaning of the last one. Can you clarify?

@@SQLBI What I mean is that there is a notion circulating that if you embed a DAX expression in two CALCULATEs that is the equivalent of a measure. CALCULATE(CALCULATE(expression, filter_1, … filter_n)) The justification for this would be that no row contexts are visible to neither the expression nor the filter arguments. If this was correct you should be able to reference a variable declared outside a measure from within a measure. So if you have a calculated column with this DAX expression: VAR aVariable = something RETURN CALCULATE(CALCULATE(expression * aVariable , filter_1, … filter_n)) and you actually could save CALCULATE(CALCULATE(expression * aVariable , filter_1, … filter_n)) as a measure you should be able to do this: VAR aVariable = something RETURN [expressionAsAMeasure] Obviously you can't. A DAX expression becomes a measure when you save the expression as a measure from a client tool that adds the appropriate Meta data AND the DAX expression is isolated from the outside world except for the current evaluation context. Measure = { measure metadata + evaluation context + DAX expression } So a measure is a closed deal (a Closure?). I probably shouldn't have written the last paragraph in my original comment :-)

Yes and no. You can replace any measure with CALCULATE ( ) However, you cannot replace CALCULATE ( ) with [m] where m := , because the visibility of the variables is different. This has nothing to do with the filter context and the row context, but just with the scope of the variables, that is always local to an expression. I hope it helps!

For example, this calculation should be in a measure, otherwise it cannot be aggregated correctly in a grand total if calculated row by row: Margin % = [Margin] / [Sales Amount]

@@SQLBI I use Power Pivot exclusively for outputting tables and pivot tables to the workbook and so far cannot find a use for measures. Maybe this is different in Power BI reports.

Well that's frustrating but the fact that power query can't store measures there had to be probably different languages. Also the syntax for measures is quite different from columns which is another annoying thing.