Same dimension to be used in slicer and data item

[MFGF]

rd152343 wrote in a private message:

Hi Sir,

Sorry to disturb you through private message. I am stuck at this point and not able to proceed.

Requirement is to filter on date value (June 15 to August 15), but the display should be based on Month. Data displayed for the Month should be based on
June 15 to August 15.

Have added a slicer on date level and used Month level in the reporting, but am getting an warning
"XQE-WRN-0066 The query ''<query>'' contains members in a slicer/context filter that are also projected in a crosstab. The results may not be what you expect. Slicer: ''<slicer>''

I understand, I should not use the same dimension at two places, but not able to think on any other ways to achieve my requirement.

Please help.

Thanks!

Hi,

A slicer filter is intended for use where you want to filter the measure values based on members you are not displaying in your reporting container. Eg you have a crosstab showing Revenue by Year and by Product Line, and you want the Revenue to be in the context of just the USA region.

In your case, it looks like your slicer is isolating specific date members, but you want to display the corresponding month ancestors in your crosstab, along with the measure values corresponding to the chosen dates?

I would take the following approach:

- add a slicer filter with the expression set([your date level] -> ?your date parameter?)
- add a query calculation to your crosstab with the expression filter([your month level], [your measure value] > 0)

This should then show just the months corresponding to the dates selected in the slicer, and the measure values will reflect the chosen dates.

Cheers!

MF.

[rd152343]

Hi MF,

Thanks a lot for your reply. We are using a Dynamic Cube.

There is a little deviation for what you have mentioned. Have added the slicer based on date range selected in prompts.
Slicer Expression: filter(DATE, DATE_KEY between 20160201 and 20160731).
Date item (MONTH) Expression: filter(MONTH, MEASURE>0)

MONTH and measure is used on crosstab, with these expression, I still get a warning,

"XQE-WRN-0066 The query ''Query3'' contains members in a slicer/context filter that are also projected in a crosstab. The results may not be what you expect. "

I am afraid, will this warning cause any issue?

If I understand correctly, the reason for not using the same set in SLICER and DATA ITEM expression is, the cross join will result in more number of members which might exceed the property set on Dynamic Cube. Please correct me if I am wrong in my understanding.

Thanks!