Forum Discussion
Data filtering using multiple date columns of a table
Hi,
I need some approaches on the below. I have a tabular model with the table called proposals and
Suppose table proposals have different date columns like initiationdate, responsedate, completeddate.
I will consider calendar table for the date prompt in report
And I have date range filter in report where user prompts Fromdate and Todate
Now when a user selects the range in date fitler in powerbi report, the records to be displayed on the visual is from the table proposals with the multiple conditions like where initiationdate between the date range selected in date filter
or responsedate between the date range selected in date filter
or completeddate between the date range selected in date filter
| productid | initiationdate | responsedate | completeddate |
| 127 | 6/5/2017 | 6/9/2017 | 6/13/2018 |
| 128 | 6/6/2017 | 6/10/2017 | 6/14/2018 |
| 129 | 6/7/2017 | 6/15/2017 | 6/19/2018 |
| 130 | 6/8/2017 | 6/16/2017 | 6/20/2018 |
| 131 | 6/9/2017 | 6/17/2017 | 6/21/2018 |
| 132 | 6/10/2017 | 6/18/2017 | 6/22/2018 |
| 133 | 6/16/2017 | 6/19/2017 | 6/23/2018 |
| 134 | 6/17/2017 | 6/20/2017 | 6/24/2018 |
| 135 | 6/18/2017 | 6/21/2017 | 6/25/2018 |
| 136 | 6/19/2017 | 6/22/2017 | 6/26/2018 |
| 137 | 6/20/2017 | 6/23/2017 | 6/27/2018 |
| 138 | 6/21/2017 | 6/24/2017 | 6/28/2018 |
| date prompt | |
| From | To |
| 6/7/2017 | 6/14/2017 |
| Expected Output: | |||
| productid | initiationdate | responsedate | completeddate |
| 127 | 6/5/2017 | 6/9/2017 | 6/13/2018 |
| 128 | 6/6/2017 | 6/10/2017 | 6/14/2018 |
| 129 | 6/7/2017 | 6/15/2017 | 6/19/2018 |
| 130 | 6/8/2017 | 6/16/2017 | 6/20/2018 |
| 131 | 6/9/2017 | 6/17/2017 | 6/21/2018 |
| 132 | 6/10/2017 | 6/18/2017 | 6/22/2018 |
Please let me know the approach/ideas how shall i design the model and come up with the query
Thanks
3 Replies
Hi marri,
Two approaches here. You can download the demo and try it out.
1. Add a measure and filter the values of the measure. (No relationship between two tables.)
Measure = VAR initdate = MIN ( Table1[initiationdate] ) VAR respdate = MIN ( Table1[responsedate] ) VAR comdate = MIN ( Table1[completeddate] ) RETURN IF ( ( initdate >= MIN ( 'Date Prompt'[Date] ) && initdate <= MAX ( 'Date Prompt'[Date] ) ) || ( respdate >= MIN ( 'Date Prompt'[Date] ) && respdate <= MAX ( 'Date Prompt'[Date] ) ) || ( comdate >= MIN ( 'Date Prompt'[Date] ) && comdate <= MAX ( 'Date Prompt'[Date] ) ), 1, 0 )2. Unpivot the table Proposals. (relationship to Date table is needed.)
Best Regards,
Dale
Thankyou for the reply.
I have tried the same way as mentioned in your approach 1( without relationship between time(calendar) and proposals table)
But could not able to see the expected result.
Please find the below screen shot
Always Measure displays the value with "0" even though the product_sid's initiationdate, responsedate, completedate falls within the range of date prompt. For example, please verify for the product_sid 35659008, 35659015. The formula which is used is mentioned below
Measure = VAR initiationdate = MIN(proposals[initiationdate]) VAR responsedate = MIN(proposals[responsedate]) VAR completedate = MIN(proposals[completedate]) RETURN IF (
(
initiationdate >= MIN ( 'time'[Date] )
&& initiationdate<= MAX ( 'time'[Date] )
)
|| (
proposeddate>= MIN ( 'time'[Date])
&& proposeddate<= MAX ( 'time'[Date])
)
|| (
completedate >= MIN ( 'time'[Date])
&& completedate <= MAX ( 'time'[Date] )
),
1,
0
)Please help me in understanding why does always measure shows the value 0 instead of displaying 1 even for the records which falls into the date range prompt.
Approach2:
Proposals table is very large data set with minimum of 16 millions of records. Does unpivot of table proposals and having the relationship with time table works? Please clarify.
Hi marri,
Did you try out the demo I shared? Can you share your pbix file? It should work.
Best Regards,
Dale
