Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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:
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
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.
@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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
21 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
23 |