Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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.
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |