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 moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
Hi,
I've been struggling with the following calculation and am hoping can find possible solutions!
As the sample data show, I already have the data of customers' visit date, customer id, and the visit sequence of that customer.
The last column, Consecutive_cals is my desired output
Visit Date Time | Cus_id | appt_status | Sequence | Completed_Appt_Sequence | Consecutive_calcs |
5/25/2022 | 6448 | Completed | 1 | 1 | 0 |
6/1/2022 | 6448 | No Show/ Cancel | 2 | 1 | 1 |
6/2/2022 | 6448 | No Show/ Cancel | 3 | 1 | 2 |
6/3/2022 | 6448 | Completed | 4 | 2 | 0 |
6/8/2022 | 6448 | No Show/ Cancel | 5 | 2 | 1 |
6/21/2022 | 6448 | No Show/ Cancel | 6 | 2 | 2 |
6/24/2022 | 6448 | No Show/ Cancel | 7 | 2 | 3 |
6/29/2022 | 6448 | No Show/ Cancel | 8 | 2 | 4 |
6/30/2022 | 6448 | No Show/ Cancel | 9 | 2 | 5 |
6/5/2022 | 3221 | No Show/ Cancel | 1 | 0 | 1 |
6/7/2022 | 3221 | Completed | 2 | 1 | 0 |
6/9/2022 | 3221 | Completed | 3 | 1 | 0 |
7/3/2022 | 3221 | No Show/ Cancel | 4 | 1 | 1 |
7/11/2022 | 3221 | No Show/ Cancel | 5 | 1 | 2 |
I am looking for a solution that could calculate the consecutive occurrences of a customer for no-show appointments. Moreover, once there is a "completed" appointment occurred, the calculation will reset. Thank you in advance!
Solved! Go to Solution.
Hi,
I am not sure if I understood your question correctly, but please check the below measure and the attached pbix file.
It is for creating a measure.
Consecutive calcs measure: =
VAR _newtable =
ADDCOLUMNS (
ALL ( Data ),
"@condition", IF ( CALCULATE ( MAX ( Data[appt_status] ) ) = "Completed", 0, 1 )
)
VAR _result =
SUMX (
FILTER (
_newtable,
Data[Cus_id] = MAX ( Data[Cus_id] )
&& Data[Completed_Appt_Sequence] = MAX ( Data[Completed_Appt_Sequence] )
&& [@condition] <> 0
&& Data[Visit Date Time] <= MAX ( Data[Visit Date Time] )
),
[@condition]
)
RETURN
IF (
HASONEVALUE ( Data[Visit Date Time] ),
IF ( MAX ( Data[appt_status] ) = "Completed", 0, _result )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am not sure if I understood your question correctly, but please check the below measure and the attached pbix file.
It is for creating a measure.
Consecutive calcs measure: =
VAR _newtable =
ADDCOLUMNS (
ALL ( Data ),
"@condition", IF ( CALCULATE ( MAX ( Data[appt_status] ) ) = "Completed", 0, 1 )
)
VAR _result =
SUMX (
FILTER (
_newtable,
Data[Cus_id] = MAX ( Data[Cus_id] )
&& Data[Completed_Appt_Sequence] = MAX ( Data[Completed_Appt_Sequence] )
&& [@condition] <> 0
&& Data[Visit Date Time] <= MAX ( Data[Visit Date Time] )
),
[@condition]
)
RETURN
IF (
HASONEVALUE ( Data[Visit Date Time] ),
IF ( MAX ( Data[appt_status] ) = "Completed", 0, _result )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim ,
Yes! Your approach is exactly the solution I am looking for:)
I appreciate your help! Thanks a lot!!
User | Count |
---|---|
93 | |
92 | |
85 | |
83 | |
49 |
User | Count |
---|---|
150 | |
142 | |
112 | |
73 | |
55 |