Data filtering using multiple date columns of a table

[marri]

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

[v-jiascu-msft]

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

[marri]

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.

[v-jiascu-msft]

Hi @marri,

Did you try out the demo I shared? Can you share your pbix file? It should work.

Best Regards,

Dale