Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I’m having trouble applying two date filters alongside multiple report-level filters. I’ve tried multiple methods: DATESBETWEEN, DATESINPERIOD, FILTER, CALCULATE, ALL, ALLEXCEPT, IF, KEEPFILTERS, REMOVEFILTERS, etc. I can get individual components working properly, but not everything working correctly at the same time.
The pbix file can be downloaded from OneDrive at https://1drv.ms/u/s!AizFaawJP_Ads2L7sbT5o1qBm0Dv?e=PiL0Kn
Background & Requirements
Current Functionality
The required functionality is currently achieved using two iterations of the data table('OrdersTestData'), two iterations of the date table ('DateTable'), and six lookup tables that power six of the seven report-level filters. It is a cumbersome, inefficient solution.
Goal: Achieve required functionality using only tables 'OrdersTestData' and 'DateTable.'
The pbix file can be downloaded from OneDrive at https://1drv.ms/u/s!AizFaawJP_Ads2L7sbT5o1qBm0Dv?e=PiL0Kn
Thank you in advance for helping educate me! 🙂
Hello Kelly,
Thank you for taking the time to offer a solution. I downloaded your pbix; however, I have not been able to achieve the desired results yet. I need a little guidance from you or other forum viewers on these items, please.
To clarify, tables ‘OrdersTestData’ and ‘OrdersTestData13Month’ contain the exact same data. In Power Query, ‘OrdersTestData13Months’ is “Referenced” from ‘OrdersTestData.’ It is named ‘OrdersTestData13Months’ as a reminder that its data is always filtered to the previous 13 calendar months.
Using my original pbix, the examples below demonstrate the filtering at work. I am hoping to maintain that functionality without requiring “extra” table ‘‘OrdersTestData13Months.’
Hopefully the examples below help clarify the functionality and desired end result.
Example 1:
Example 2:
Side note: The actual report I am trying to simplify contains many more data tables.
Forum viewers, I am open to alternate solutions you have to offer. Even responding, “This is not possible with the current version.” would be helpful to know.
Thank you! 🙂
Hi @pbiibp ,
Create a date table as below:
Date table = DISTINCT(UNION(DISTINCT('OrdersTestData'[Order Date]),DISTINCT('OrdersTestData13Months'[Order Date])))
Then create a relationship between table "date table" and "OrdersTestData","date table" and "OrdersTestData13 Month".
Finally you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |