Forum Discussion
Advocate IDates Driving me Crazy
Iave two tables in a semantic model and am trying to create a relationship based on date fields but no matter how I set it up - if i try to filter in the report - my report tables either lose all data or only filter one side of the relationship. I have confirmed that the date formats, data types, relationships, and other elements are correctly configured and aligned in the Power BI semantic model.
I have also tried introducing a 3rd Date table into the model and running everything from that but get the same problem. Would be great if anybody had some ideas on something I might have missed?
Hi Alboy,
Thank you for reaching out to the Microsoft fabric community forum. Thank you danextian, govind_021, for your inputs on this issue.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.
Dax Measure for Total returns in returns table:Total Returns = SUM(Returns[ReturnAmount])Dax Measure for Total sales in sales table:
Total Sales = SUM(Sales[Amount])Relationship:
Outcome:
I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
7 Replies
v-kpoloju-msftCommunity Support
Hi Alboy,
Thank you for reaching out to the Microsoft fabric community forum. Thank you danextian, govind_021, for your inputs on this issue.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.
Dax Measure for Total returns in returns table:Total Returns = SUM(Returns[ReturnAmount])Dax Measure for Total sales in sales table:
Total Sales = SUM(Sales[Amount])Relationship:
Outcome:
I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
AlboyThanks for that. I have tried however the filtering only works on the Period Plan table - evrything from Order2Install table is omitted:
I can work around by just deleting all relationships and adding filters for each table - just a little bit messy is all but looks like I'll need to move on and just do that 🙂 Thanks all for the assist 🙂
- Alboy
I take it back - I used a new data table (like yours) as opposed to the generic one I use (which combines data with period, Financial Period etc) and it worked perfectly - thank you!
- govind_021
Super User
Hi Alboy
If you have multiple active relationships to the same Date table, conflicts can arise. Ensure only one relationship to the Date table is active at a time (Power BI allows only one active relationship). You can manage inactive relationships via DAX using functions like USERELATIONSHIP().
Best Regards
Govind Sapkade ( Microsoft Certified Data Analyst , PL 300 Certified , MS Fabric Enthusiast)
Let's Connect
Linkdin - www.linkedin.com/in/govind-sapkade-845104225
Youtube - http://www.youtube.com/@govind_dataanalyst
