Using a date filter to capture rows within a date range, when multiple date columns are involved

[cferv_7]

Hello everyone, 

First time posting here and new to PBI Desktop. My problem relates to dates and filters. I have searched and looked at several links, but I could not find one that match my particular case. 

I have an end-user who wants a date slicer/filter that pickups any row/record that has an 'activity' within the selected date range of their choice. For the table example down below, if the person wants to see any activity between 12/10/2021 and 12/15/2021, then the last three rows appear. I can see this being done in Excel with two blank reference cells, filled with date_1 (min) and date_2 (max), with an additional boolean-type column that returns 1 or 0 down the rows if any of the fields within that row has a date within the entered date range. However, I cannot imagine how to do this in PBI Desktop (with DAX) because there needs to be some type of date relationship between the date filter and any of those date-type fields (to my understanding).

I tried making a separate date field with CALENDERAUTO, using it as a filter by itself with no connection, in a combination with a DAX conditional boolean column (if column  > date.min & column < date.max; doing this for all the date-related columns), but it did not give me the results I am looking for (unless I executed the idea poorly). Creating a relationship between the calendar_date and Request_Date didn't help because the records that have the dates would get filtered out, and the same if I link it to Last_Updated instead. 

So is this idea even possible in PBI (using DAX)? Apologies in advance if a similar problem was posted in the past. 

ID	Request_Date	Quote_Issued	Charges_Sent	A_Issued	B_Sent	C_Received	Last_Updated
1	11/1/2021	11/9/2021	11/2/2021	11/4/2021	11/3/2021	11/6/2021	11/15/2021
2	12/1/2021	12/21/2021	12/12/2021	12/15/2021	12/16/2021	12/27/2021	12/31/2021
3	12/2/2021	12/21/2021	12/15/2021	12/18/2021	12/19/2021	12/27/2021	12/31/2021
4	12/3/2021	12/15/2021	12/12/2021	12/16/2021	12/20/2021	12/27/2021	12/31/2021

[jdbuchanan71 · Accepted solution]

@cferv_7 

I changed our filter formula a bit to give us a 1 on each return row.

Dates Filter = 
    VAR _Request =  CALCULATE ( COUNTROWS ( Data_Table ) )
    VAR _Quote =    CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Quote_Issued] ) )
    VAR _Charge =   CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Charges_Sent] ) )
    VAR _Issued =   CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[A_Issued] ) )
    VAR _Sent =     CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[B_Sent] ) )
    VAR _Received = CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Charges_Sent] ) )
    VAR _Updated =  CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Last_Updated] ) )
RETURN IF ( NOT ISBLANK ( _Request + _Quote + _Charge + _Issued + _Sent + _Received + _Updated ), 1 )

Then we can use it in a SUMX to get the count of records that match.

Record Count = SUMX ( ALL ( Data_Table[ID] ), [Dates Filter] )

I have attached my updated file.

[cferv_7]

Thank you again @jdbuchanan71 ! This works great! I tested it on a few small date-ranges on my original dataset, but noticed something weird after I included the maximum date range. The total count was slightly off. After putting the measure in a table and exporting the data to Excel to do a count there, the total given in Excel is correct but somehow the number displayed in PBI is wrong. 

So this appears to be a visual bug and I will look further into. If you happen to have some sources that I can start with to resolve this issue, that would be very much appreciated. I will mark your reply as the solution. Thank you again. 

[cferv_7]

Hello @jdbuchanan71 , thank you so much for this! This is great! 

However, I am running into the issue of trying to get a count of the number of rows that fit the date criterion as well. I tried to use your DAX formula to create a calculated column and then convert the result to a Count when using a card visual. But the result doesn't seem to be accurate. 

Then I tried to see what your measure would look like on a card visual but it gives the sum (of the number of instances). I tried to wrap the return statement in an IF statement that will return 1 with no else clause. This gave the same result but when placed on a card visual, it gives me the average. 

Is there a way to get the number/count of rows for Dates Filter to show on a card visual? Please and thank you.

[parry2k]

@cferv_7  @jdbuchanan71 has provided a great solution but if I have to do this I will unpivot the table (which is always a best practice) and then I have one relationship with date table and I can slice on any date and also on any date column. 

To achieve this:

Transform Data -> Select ID column -> Right Click -> Unpivot other columns

you will get two columns, Attribute and Value, rename these as you see fit, the attribute will have column names, and value will be date.

Close and apply.

Set relationship between date table and transaction table, and from here onward everything should be straightforward.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[jdbuchanan71]

@cferv_7 

One way to do it is to create a relationship between your Dates table (every model should have a single master dates table) and every one of the date fields.  Only one of them can be active but we can active the others in a measure.

In my example the relationship to the Request_Date is the active one but that is just becuase it was the first one in the table.

Then we write a measure to count the rows for each of the date fields.

Dates Filter = 
    VAR _Request =  COUNTROWS ( Data_Table )
    VAR _Quote =    CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Quote_Issued] ) )
    VAR _Charge =   CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Charges_Sent] ) )
    VAR _Issued =   CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[A_Issued] ) )
    VAR _Sent =     CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[B_Sent] ) )
    VAR _Received = CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Charges_Sent] ) )
    VAR _Updated =  CALCULATE ( COUNTROWS ( Data_Table ), USERELATIONSHIP ( Dates[Date],Data_Table[Last_Updated] ) )
RETURN _Request + _Quote + _Charge + _Issued + _Sent + _Received + _Updated

USERELATIONSHIP activates the link between the Dates table and the Data_Table for that particular field.  The first one  (_Request) does not need it becuse that is the primary active relationship.

When we put that measure in the table and applya filter on the Date table it will return only for rows where atleast one of the fields fall into the date range.

I have attached my sample file for you to look at.