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
