cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
a4appl-e
Frequent Visitor

Issue with one to many Bi Directional filter missing LY values

Hello everyone,
I am just testing a simple data model with which has a Bi-directional filter from a date table to a date periods table ( I know it is bad 🙂 ) . 
Sales -> Dates ( Many to One ). Dates is a continuos calendar table with atleast 25 years of data. Sales has 4 years worth of data at a daily level.
Dates -> DatePeriods ( One to One ) : Bi-Directional
Measure 1 : Sales Amount = SUM( Sales[SalesAmount] )
Measure 2 : Sales Amount LY = CALCULATE( [Sales Amount], DATEADD( Dates[Calendar_Date], -364, DAY ) )

DatePeriods table has data for dates like Last Week, This Week, Next week.( Yes, can be done with relative filters )
When I use 1 to 1 between Dates and Dateperiods ( No intersecting dates in any of the Dateperiods ), Sales Amount LY works real file.
Now when I change the One to One to One to Many, it doesn't show LY Values any more and just goes blank. 

I wanted to understand why this happens and what can be done to see the LY values with the above mentioned scenario.

Any links to read or solution for this will be really helpful. Thanks in advance.


Power BI File => https://app.box.com/s/k1ggznmd5vekguuvfihvqi5mls30u32p

 

Update:
I have attached the power bi file. There are 2 relationships I created to illustrate the example. I am interested in the one to many since I will have intersecting dates in Dateperiods table.

1 ACCEPTED SOLUTION

Hi @a4appl-e,

 

The problem is with the measure defintion. DAX time intelligence functions, DATEADD is used in the measure , automatically replace all date related filters by a new date filter. Date related filters are from columns in the calendar table specified in the DATEADD function as well as columns in all related tables by following many-to-one (or one-to-one) relationships, but not one-to-many relationships. As a result, the filters from 'DatePeriods(1 to Many)' will not be automatically removed by DATEADD but must be removed explicitly like below:

 

CALCULATE(
    [Sales Amount],
    DATEADD( 'Calendar'[Date], -364, DAY ),
    REMOVEFILTERS('DatePeriods(1 to Many)')
)

 

Here is the output:

2.png

 

Best Regards,

Link

View solution in original post

7 REPLIES 7
v-xulin-mstf
Community Support
Community Support

Hi @a4appl-e,

 

I have reproduced the situation you describe in my environment.

I have reported it internally.

I will update here when I got any response.

 

Best Regards,

Link

Thank you for confirming. will look forward for an answer.

Hi @a4appl-e,

 

I have not heard from you for a long time, if your issue solved?

 

Best Regards,

Link

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Thank you. This has worked and accepted the post as solution.

Hi @a4appl-e,

 

The problem is with the measure defintion. DAX time intelligence functions, DATEADD is used in the measure , automatically replace all date related filters by a new date filter. Date related filters are from columns in the calendar table specified in the DATEADD function as well as columns in all related tables by following many-to-one (or one-to-one) relationships, but not one-to-many relationships. As a result, the filters from 'DatePeriods(1 to Many)' will not be automatically removed by DATEADD but must be removed explicitly like below:

 

CALCULATE(
    [Sales Amount],
    DATEADD( 'Calendar'[Date], -364, DAY ),
    REMOVEFILTERS('DatePeriods(1 to Many)')
)

 

Here is the output:

2.png

 

Best Regards,

Link

View solution in original post

a4appl-e
Frequent Visitor

@amitchandak Thanks for your reply.
https://app.box.com/s/k1ggznmd5vekguuvfihvqi5mls30u32p

I have uploaded the sample data file to box and the link is above. In the Page 2 of the report the LY Sales measure is blank and I actually am looking for the value.
I create 2 Date Periods tables just to illustrate my question. It works with 1:1 mapping and doesn't work with 1:Many mapping. You can remove the relationships and test also.

Please let me know if this helps.
Thanks in advance

amitchandak
Super User IV
Super User IV

@a4appl-e , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

The best is to have Star Schema, You should merge Date and Date period into one.

Ideally, 1-M from date period to date and Date to Sales should have worked.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors