Forum Discussion

Qianru221's avatar
Qianru221
Icon for Helper II rankHelper II
8 years ago

Measures not shown in query mode

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!

 

Need Help
Show and Tell
Tips and Tricks

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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's avatar
      Qianru221
      Icon for Helper II rankHelper II
      8 years ago

      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.