Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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 parti...
Need Help
Show and Tell
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    Anonymous 

    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.

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.