Issue with visual level filters and ParallelPeriod Function (MDX)

Question

Hello Comunity,&nbsp;We use live connection to MOLAP Cube, and have the strange issue with visual level filters and previous periods based data.In the cube the measure is defined asCREATE MEMBER CURRENTCUBE.[Measures].[MyMeasurePM]&nbsp;AS ([Measures].[MyMeasure],&nbsp;&nbsp; &nbsp;ParallelPeriod(&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;[Period Date].[Year-Month-Date].[Cal Year Month],1,&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;[Period Date].[Year-Month-Date].CurrentMember))&nbsp;We also have the delta measure defined as MyDelta = [MyMeasure]-[MyMeasurePM].We also have the flag in our Date dimension that indicates the month is one of the last twelve month.When doing a simple test - selecting calendar months, and all three meaures, all the data is shown correctly.However, when applying the VISUAL filter IsLastTwelveMonth=1, the first month MyMeasurePM value is blank,and therefore the MyDelta is equal to MyMeasure value.So it looks like the the filter is applied not to the visual, but to the DATASET!Why?! Should't the VISUAL filter have no effect on the source data?&nbsp;I am, of course, aware how to go around this by extending the period or using the relative date filtering, but these are workarounds, not the correct behaviour!&nbsp;Is this a bug in PowerBI or an expected behaviour?!

v-huizhn-msft · Answer

Hi&nbsp;AnGorSan,When you upload data to Power BI desktop, data is imported to Power BI. When you use VISUAL filter,&nbsp;filter will impact the visual. Please refer to this thread(https://community.powerbi.com/t5/Desktop/Using-Measure-as-a-Visual-level-filter/td-p/436847) for more details.Thanks,Angelia

angorsan · Answer

Hi Angela.&nbsp;Thank you for your reply. However, it makes no sense in regards to the question because:1 - We do not import data, we use LIVE connection2 - We do not filter the visual by its measures, we use date dimension attribute as a filter.&nbsp;The issue here is that the Power BI visuals seem to filter the datasets underneath them, and then do the display/calculations.However, the calculations should be performed PRIOR to VISUAL filter application!&nbsp;My question is - why is it implemented in a way the PARALLELPERIOD functions become 50% useless ???&nbsp;

v-huizhn-msft · Answer

Hi&nbsp;AnGorSan,When you use Direct Query,&nbsp;Power BI Desktop&nbsp;queries the underlying data source, which means you’re always viewing current data. You said "the Power BI visuals seem to filter the datasets underneath them", in your dataset, the data is not filtered?
&nbsp;
Thanks,Angelia

angorsan · Answer

Hi Angela,&nbsp;I do not understand why you are using the term "Direct Query"."Direct Query" is the connection mode to the SQL databases.&nbsp;We are using "LIVE CONNETCION" connection mode, to connect to the ANALYTICAL database, which is MOLAP cube.I am saying, again, that our initial dataset is not filtered, and provides data for multiple years.In my dataset there are measures that are calculated using MDX function PARALLELPERIOD.So when I use a VISUAL filter, I expect it to be applicable to the data it should display, not to the query that is being sent to SSAS cube.&nbsp;However, instead, the filter is applied to the DATASET, making the MDX function PARALLELPERIOD ineffective for the first displayed period, because it filters out the value required for the calculation!&nbsp;

v-huizhn-msft · Answer

Hi&nbsp;AnGorSan,In your scenario,&nbsp;the filter is applied to the DATASET, so the date column used in&nbsp;PARALLELPERIOD&nbsp;are still continuous? The date should form a contiguous interval, please review to this thread.
&nbsp;
Best Regards,Angelia

Forum Discussion

Issue with visual level filters and ParallelPeriod Function (MDX)

6 Replies

Related content

Caclulate function over riding filter?

filter function

PARALLELPERIOD Filter Problem

Parallelperiod function issue

PARALLELPERIOD vs DATEADD