Forum Discussion

udian's avatar
udian
Icon for Helper III rankHelper III
10 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
    10 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
Icon for Community Champion rankCommunity Champion
10 years ago

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

  • udian's avatar
    udian
    Icon for Helper III rankHelper III
    10 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
      Icon for Microsoft Employee rankMicrosoft Employee
      10 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
        Icon for Helper III rankHelper III
        10 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!