Forum Discussion

ValeriaBreve's avatar
ValeriaBreve
Post Partisan
1 year ago

Showing countrows with zero rows for a selected date range

Hello,

I have a table called Date_Table_Action which has a 1->* relationship to table BBP_Actions on the date.

 

I need to display a table that has all the continous dates between the min date on BBP_Actions and the max date on BBP_Actions and the related number of actions (setup is 1 row per action in BBP_Actions).

If there are no actions on that day, it should show zero.

I don't seem to be able to get there... this is my measure:

 

# Count of Actions =
VAR _FilteredDateTable=
CALCULATETABLE(Date_Table_Action,
FILTER(Date_Table_Action,Date_Table_Action[Date]<=MAX(BBP_Actions[ActionCompleteTargetDate])),
FILTER(Date_Table_Action,Date_Table_Action[Date]>=MIN(BBP_Actions[ActionCompleteTargetDate])))
RETURN
CALCULATE(
    COUNT(BBP_Actions[Id]),_FilteredDateTable)

 

In my table, I have all date related fields coming from Date_Table_Action, and the only added field is # Count of Actions.

I only get this in return: either only the dates that have actions (normal), or when I set the "Show Items with no data" to true, then I get the whole range of dates from Date_Table_Actions which is far too wide - it should only be the range as per filter in the measure.

 

Can you please help me understand why this happens and how to modify the  measure to show 1 row per date in the range I wish?

 

Thanks a lot in advance!

Kind regards

Valeria

3 Replies

    • ValeriaBreve's avatar
      ValeriaBreve
      Post Partisan
      1 year ago

      SachinNandanwar I don't think I can attach anything unfortunately. For the Date_Table_Actions it would be a simple date table:

      For the BBP_Actions table, here is some sample data from the table. Relationship is 1 --> * from Date_Table_Action[Date] to BBP_Actions[ActionCompleteTargetDate]

      IdStatusActionCompleteTargetDate
      67Validated12-Sep-24
      68New18-Sep-24
      69Study Ongoing26-Sep-24
      70New12-Sep-24
      71New19-Sep-24
      72New12-Sep-24
      77Completed17-Sep-24
      78New17-Oct-24
      79New26-Sep-24
      80New25-Sep-24
      81New26-Sep-24
      82New24-Sep-24
      83New26-Sep-24
      84New26-Sep-24
      85New26-Sep-24
      86New30-Sep-24
      87New30-Sep-24
      88New01-Oct-24
      89New01-Oct-24
      90New10-Oct-24
      91New17-Oct-24
      92New17-Oct-24
      93New 
      94New30-Oct-24
      98New 
      99New 
      100New 
      101New 
      102New 


      Thanks!

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi ValeriaBreve ,

    Based on the description, try using the following DAX formula.

    Count of Actions =
VAR _MinDate = MIN(BBP_Actions[ActionCompleteTargetDate])
VAR _MaxDate = MAX(BBP_Actions[ActionCompleteTargetDate])
VAR _DateRange = 
    FILTER(
        Date_Table_Action,
        Date_Table_Action[Date] >= _MinDate &&
        Date_Table_Action[Date] <= _MaxDate
    )
RETURN
SUMX(
    _DateRange,
    CALCULATE(
        COUNT(BBP_Actions[Id]),
        BBP_Actions[ActionCompleteTargetDate] = Date_Table_Action[Date]
    )
)

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.