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

udian's avatar
udian
Icon for Helper III rankHelper III
10 years ago

Hi Herbert,

 

Thanks for your patience!

 

I'll try the explain so that you can understand what I'm trying to achieve.

 

I have a leading month slicer that reffers to a dimension table containing 6 records, one for each of the last 6 months. The slicer can be sliced on a single value only. The slicer should filter some of the charts in the report on the chosen month.

The issue starts when i want to use the same slicer as a reference point for some of the other charts. on these charts i which to display data for the last 12 months prior to the month chosen in the slicer.

 

For example, in the image below i would expect to see in the table the measure values for 8/2015 - 7/2016 (total of 12 rows).

The date in the table rows is taken from the full time dimension. The months in the slicer are taken from the Last_6_Months dimesion that is connected to the full time dimension with the MonthDate column (date of the first day of the month).

 

I tried to use the Selected Month Measure from your last answer and use it in  my measure with no success.

 

Hope that my explanation is clear.

 

Thanks!

v-haibl-msft's avatar
v-haibl-msft
Icon for Microsoft Employee rankMicrosoft Employee
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

  • udian's avatar
    udian
    Icon for Helper III rankHelper III
    10 years ago

    Thanks for all your help!