Power BI Can't Select field in DAX Calculation

Question

I have the table Fact_Sales below with flags against every line to indicate if there are sales TY and LY.

I want to create a DAX Calculation something like below to return a value if both fields = 1.

Net Sales LFL = IF([Sales_in_Week_Flag_TY]=1 && [Sales_in_Week_Flag_LY]=1,[NET Sales],0)

I can't select the fields [Sales_in_Week_Flag_TY] in DAX e.g. when I type in DAX 'Fact_Sales'[ the fields are not in the drop down to select.

Why would they not show?

Answer 1

Do it using CALCULATED COLUMN():

Answer 2

There are different types of DAX "calculations" that you can make. It looks like you are trying to create a DAX measure. Measures are not like the calculated columns in DAX. Measures are calculated in some context, which can be more than one row, while calculated columns are calculated in the context of one specific row. When you are writing DAX code in a calculated column, you can write TableName[FieldName] and get the value of this column, because there is only one row in the context, thus only one value. With measures however, usually there are multiple rows in the context, that's why you must specify how to aggregate the values, e.g. by summing them up (Net Sales LFL = SUM(Fact_Sales[NET Sales])). It is also important where exactly you are trying to reference the column - in some places a column reference is allowed/expected (like as a parameter of an aggregating function), and in other places you can't reference a column directly.

In your case, to calculate the net sales, you can write a measure using CALCULATE function, and provide the calculation as the first parameter (SUM) and the filter in the second parameter:

Net Sales LFL = CALCULATE(SUM(Fact_Sales[NET Sales]),
    Fact_Sales[Sales_in_Week_Flag_LY] = 1 && Fact_Sales[Sales_in_Week_Flag_TY] = 1)