Forum Discussion

sagar512's avatar
sagar512
Helper I
4 years ago
Solved

Count Rows Falling between two dates ( from disconnected table )

Hi, I am having two disconnected tables ( Task and Ticket). On Task Table I need to count tickets (from the Ticket table) that fall between the task created date and Created +90 date.   In Excel,...
Need Help DAX measure
  • smpa01's avatar
    smpa01
    4 years ago

      I still don't see an issue here 

     You need to display 

    'Table 1'[Created] and 
    'Table 1'[Created +90] in the viz for the DAX to evaluate in the filter context. Without having that axis present, the expression will not evaluate.
     
     
    But if you do want to have that one displayed without bringing
    'Table 1'[Created] and 
    'Table 1'[Created +90]
    then you need an index column and do this with a new measure

     

ValtteriN's avatar
ValtteriN
Community Champion
4 years ago

Hi,

Here is one way to do this:

Dax (calculated column):

Tickets = calculate(COUNT(Tickets[TN]),DATESBETWEEN('Calendar'[Date],'Task Table'[Created],'Task Table'[C90]))

Data model:

 

End result:

I hope this helps and if it does consider accepting this as a solution and giving a thumbs up!

  • sagar512's avatar
    sagar512
    Helper I
    4 years ago

    Thanks for the response; but unfortunately I cannot use the calculate function in the column, as I am working with powerbi data set (direct query). 

    Any way to do this in measure?

    • ValtteriN's avatar
      ValtteriN
      Community Champion
      4 years ago

      Hi,

      It works almost exactly the same with a measure: 

      tickets_ =
      var _sdate = max('Task Table'[Created])
      var _edate = max('Task Table'[C90]) return


      calculate(COUNT(Tickets[TN]),DATESBETWEEN('Calendar'[Date],_sdate,_edate))

       

      • sagar512's avatar
        sagar512
        Helper I
        4 years ago

        thanks, but the problem with the above solution is I need to always keep the lowest level (Task number) into the context.


        my end goal is to build some chart that aggregates to month level etc, but calculation should always stay at task number level and then aggregate.

        I think the column was the best fit in this scenario but unfortunately, I can't use it due to direct query 😞