topic Showing countrows with zero rows for a selected date range in DAX Commands and Tips

Showing countrows with zero rows for a selected date range

ValeriaBreve — Tue, 08 Oct 2024 16:28:55 GMT

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

Re: Showing countrows with zero rows for a selected date range

SachinNandanwar — Tue, 08 Oct 2024 17:44:55 GMT

Can you please post some sample data ?

Re: Showing countrows with zero rows for a selected date range

ValeriaBreve — Tue, 08 Oct 2024 18:02:56 GMT

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]

Id	Status	ActionCompleteTargetDate
67	Validated	12-Sep-24
68	New	18-Sep-24
69	Study Ongoing	26-Sep-24
70	New	12-Sep-24
71	New	19-Sep-24
72	New	12-Sep-24
77	Completed	17-Sep-24
78	New	17-Oct-24
79	New	26-Sep-24
80	New	25-Sep-24
81	New	26-Sep-24
82	New	24-Sep-24
83	New	26-Sep-24
84	New	26-Sep-24
85	New	26-Sep-24
86	New	30-Sep-24
87	New	30-Sep-24
88	New	01-Oct-24
89	New	01-Oct-24
90	New	10-Oct-24
91	New	17-Oct-24
92	New	17-Oct-24
93	New
94	New	30-Oct-24
98	New
99	New
100	New
101	New
102	New

Thanks!

Re: Showing countrows with zero rows for a selected date range

Anonymous — Tue, 22 Oct 2024 09:47:28 GMT

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.