Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.