Forum Discussion

udian's avatar
udian
Helper III
9 years ago
Solved

All Function Ignored When Using Power BI in Direct Query Mode

Hi All,   I am using Power BI in direct query mode to query a tabular cube (the cube was created manually).   One of the measures in the cube contains ALL() function in order to ignore user filte...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    udian

     

    I don’t think this is possible in Direct Query Mode. When you select a leading month, the table will always be filtered based on your selection in the slicer, unless you edit interactions to none. But in that case, the table visual will never be filtered. As I said before, the ALL function will ignore the filter from the slicer in the measure calculation, but will not impact the table visual display.

     

    I suggest you use one more slicer like Timeline to select the last 12 months and control the rows shown in the table chart.

     

    Best Regards,

    Herbert

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

Try File | Options and settings | Options | DirectQuery -> Allow unresticted measures.

udian's avatar
udian
Helper III
9 years ago

Thanks for the quick reply.

 

Already tried that before posting...doesn't solve the problem.

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    udian

     

    I just created a simple measure which using ALL() function in Direct Query Mode, it worked well as below.

     

    Best Regards,

    Herbert

    • udian's avatar
      udian
      Helper III
      9 years ago

      Hi Herbert,

       

      Appreciate you testing the scenario on your side!

       

      I simplified my measure for simpler testing and it still doesn't work.

       

      This is my test measure:

       

      First_Loaders :=CALCULATE(1000,all(Dim_Last_6_Months))

       

      and this is the result:

      As you can see, instead of getting one row for each month with the value 1000 i get only one row for July meaning that the measure ignores the all function.

      The month slicer in the image is based on a table containing only 6 records for the last 6 months. The "Last 6 Months" dimesion is connected to the full time dimesion with the MonthDate column (MonthDate = the date of the fisrt day of the month).

       

      Any ideas why it is not working for my measure?

       

      Thanks!

      • v-haibl-msft's avatar
        v-haibl-msft
        Microsoft Employee
        9 years ago

        @udian

         

        The ALL() function will ignore the filter from the slicer in the measure calculation, but your second table will only return one row because of the slicer selection. This is actually as expected. In my above example, if I remove ALL() in Total_Sales measure, the Total_Sales column values will be same as Sales column because of row context. So the ALL() function is actually working.

         

        Best Regards,

        Herbert