Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All,
I have a an attendance table which is tied to a date table. Issue I am having is there isnt data for every single day. I want the 0 days to count e.g.
Mon - 0%
Tue - 0%
Wed - 7%
Thu - 0%
Fri - 0%
The calculation I am trying to do would be 0+0+7+0+0 = 7 divided by 5 = 1 (rounded up)
How do I edit this to include 0 days? The highlighted is the only day in that week that had attendance
Hi @Anonymous
Please try putting "+0" behind your formula
AVG % (M-F) =
VAR WeekdayTable =
FILTER(dDates, dDates[DayOfWeek] <> 6 && dDates[DayOfWeek] <> 0 && dDates[Year] = 2022)
RETURN
AVERAGEX(WeekdayTable, [DailyAttendance]) + 0
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Hey,
That adds 0 at the Year level not the day level
ok, I have overseen something. Can you please try the following:
AVG % (M-F) =
VAR WeekdayTable =
FILTER(dDates, dDates[DayOfWeek] <> 6 && dDates[DayOfWeek] <> 0 && dDates[Year] = 2022)
RETURN
AVERAGEX(WeekdayTable, [DailyAttendance] + 0)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Hey, this seems much closer than what I initially had but for the below calendar week where the highlighted falls, the answer should be 1.4% because Mon, Wed, Thu, and Friday are 0% but Tuesday is 6.6%. Im struggling to see what Im missing 😞
Hi @Anonymous
what really confues me is that you one the one hand speak about weeks but on the other hand you put single dates into the visual and in a previous picture you put year values into a bar chart.
Can you please show the data model and explain the meening of the columns in your table visual?
Best regards
Michael
I created a date table that has all the different dimensions
Can you please sho the definition of the measures you use (e.g. daily attendance or headcount)?
Because I tied my date table to the fBrazilBadge table, the tqble visual only shows dates that have data. I need to be able to show all dates even ones that have no data, that way they are included in the AverageX DAX
Hi @Anonymous
To be honest its really hard to provide a solution in this case. I assume that you have some challenges with the granularity and relations between the tables. Can you maybe provide an PBIX example with dummy data?
Best regards
Michael
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
87 | |
85 | |
75 | |
69 | |
65 |
User | Count |
---|---|
214 | |
128 | |
117 | |
82 | |
76 |