All Function Ignored When Using Power BI in Direct Query Mode

[udian]

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 filtering on time dimension.
When i query the cube using Power BI in direct query mode the measure doesn't ignore the user filtering and the measure is filtered on a single month, but when i connect to the tabular cube with excel and preform the same query (same measure and filters) i get the correct result and i can see the measure value for the diferrent months.

below you can see the query output from sql profiler for both query methods:

Power BI:

EVALUATE
TOPN(
501,
CALCULATETABLE(
ADDCOLUMNS(
KEEPFILTERS(
FILTER(
KEEPFILTERS(VALUES('Dim_Transaction_TimeTable'[MonthDate])),
NOT(ISBLANK('Fact_Volume'[First_Loaders_Last_In_6_Months3]))
)
),
"First_Loaders_Last_In_6_Months3", 'Fact_Volume'[First_Loaders_Last_In_6_Months3]
),
KEEPFILTERS(
FILTER(
KEEPFILTERS(VALUES('Dim_Last_6_Months'[MonthDate])),
'Dim_Last_6_Months'[MonthDate] = DATE(2016, 8, 1)
)
)
),
'Dim_Transaction_TimeTable'[MonthDate],
1
)

ORDER BY
'Dim_Transaction_TimeTable'[MonthDate]

Excel:

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Dim_Transaction_TimeTable].[MonthDate].[All]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM [Model] WHERE ([Dim_Last_6_Months].[MonthDate].&[2016-08-01T00:00:00],[Measures].[First_Loaders_Last_In_6_Months3]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Does anyone know the reason for this strange behaviour or how to solve this?

Thanks,

Udi

[Greg_Deckler]

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

[udian]

Thanks for the quick reply.

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

[v-haibl-msft]

@udian

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

Best Regards,

Herbert

[udian]

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]

@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

[udian]

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!

[v-haibl-msft]

@udian

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

[udian]

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!

[v-haibl-msft]

@udian

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

[udian]

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 · Accepted solution]

@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]

Thanks for all your help!