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.
Hello,
I am trying to calculate the consecutive absent count of students......like they should be absent for 7 days continuously then the dax should take the count as 1 and if another student is asbsent for continuous 7 days then, dax should increase its count to 2. I have attached the above picture for reference. Consider Dates are stacked in one single column, in the above picture i divided the date into columns for easy understanding.
Help me to create a measure/ calculated column.
https://drive.google.com/file/d/1ul3CV3LSPBy5gk8tfmDtrunqCu-eKOsj/view?usp=drive_link
The .Pbix is in the above url
Hi @v-jianboli-msft ,
Thank you for your help,
But my data seems to be big, i am getting the below error after implementing your measure.
Could you please help me with this?
Hi @YogeshS ,
Please try:
Measure =
VAR _a =
COUNTX ( FILTER ( 'Table', [Value] = 0 ), [Date] )
VAR _b =
CALCULATE (
DISTINCTCOUNT ( 'Table'[ID] ),
FILTER (
FILTER ( ALL ( 'Table' ), [ID] <= MAX ( 'Table'[ID] ) ),
COUNTX (
FILTER ( ALL ( 'Table' ), [ID] = EARLIER ( 'Table'[ID] ) && [Value] = 0 ),
[Date]
) = 7
)
)
RETURN
IF ( _a = 7, _b )
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
84 | |
84 | |
66 | |
63 | |
62 |
User | Count |
---|---|
199 | |
120 | |
110 | |
79 | |
69 |