Forum Discussion
All Function Ignored When Using Power BI in Direct Query Mode
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
Hi Herbert,
Thank you for the explanation!
So assuming that the MonthDate in the rows of the table visual is taken form the full time dimension, is there any way to display all months regardless to the selection in the Last_6_Months dimesion?
My final goal is to slice some of the visuals in my report with the chosen month from the Last_6_Months slicer while is some of the other visuals i wish to display all months prior to the chosen month.
Thanks!
If you don’t want the table visual to be sliced, just select the slicer and click “Edit Interactions” in Format tab. To exclude a visual from the interaction, click the None symbol in the upper right corner, near the filter icon. For details, please refer to this document.
Best Regards,
Herbert
I am familiar with "Edit Interactions" but i can't use it because i need the chosen month in order to display measure values for 12 months prior to the chosen month.
The original measure i was trying to built is somthing like this:
First_Loaders_Last_In_6_Months3:=CALCULATE(COUNT(Fact_Volume[Payee_Involved_Party_ID]),
Fact_Volume[Payee_Transaction_Counter_Over_1_Dollar]=1,
All(Dim_Last_6_Months[MonthDate]),
DATESBETWEEN(Dim_Time[DayDate_Time],
DATEADD(Dim_Time[DayDate_Time],-12,month),FIRSTDATE(Dim_Time[DayDate_Time])))Thanks!
I’m a little confused about your requirement. Which rows do you want to show in the table chart after you choose a month in the slicer?
If you only want to get the selected month value in the slicer, you can use a measure like following.
Selected Month = IF ( HASONEVALUE ( Dim_Last_6_Months[MonthDate] ), VALUES ( Dim_Last_6_Months[MonthDate] ) )
Best Regards,
Herbert
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!
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
Thanks for all your help!