Measures not shown in query mode

[Qianru221]

Hi all,

I have a query which contains records of product sales each day (there are multiple sales record for each day)

I then created another query which I summarise the sales for each day (so that one coloumn is the date and the other is sum of product sales on that date) using a formular create a new measure.

Quantity_summed =
CALCULATE (
    SUM ( 'Sales'[Quantity] );
    FILTER ( ALL('Sales'); and('Sales'[Date] =  'Sales used for R code'[TimeDay].[Date]; 'Sales'[Customer Country]="US")
))

However the measure coloumn does not appear in the Edit Query mode! I need the column to be there so that I can run R code in the data transformation in Query mode to build a forecast model.

I tried with add a custom column in the query mode but the formular cannot be used.

Is there a method that the measure can be shown in the Query mode?

Thanks in advance!

[v-yuta-msft]

Hi Qianru221,

You should use calculate column instead of measure because calculate column is contained in data model which can be seen in Query Editor after applied. However, a measure is in visual level which means it makes no effect on data model.

In Power BI desktop, both calculated columns and measures created on report level will not be applied to Query Editor. In your scenario, you need to use Power Query to create a custom column in Query Editor with the same logic.

Reference:

https://msdn.microsoft.com/en-us/library/mt211003.aspx

In addition, in Query Editor, if you add a custom column, you should use power query formula language, not DAX.

Best Regards,

Jimmy Tao

[Qianru221]

Hi Jimmy,

@v-yuta-msft Thank you for you reply.

I tried with adding custom column in the Query. However I have to use variables that come from other data queries and they can't be found when I was formulating the formula.....what should I do then?

Best regards,

Qianru.