topic Re: How to count with condition and restart again count in DAX Commands and Tips

How to count with condition and restart again count

kongmanee — Mon, 06 Jun 2022 04:43:44 GMT

I have data in the column like C, NC and N/A

I would like to count continuous of C in this column and it will be restarted again count when appeard only NC.

So, how can I make a table or measure for this.

Re: How to count with condition and restart again count

AlB — Mon, 06 Jun 2022 05:38:19 GMT

Hi kongmanee

Can you show a sample of your data? It needs to have a column to establish order

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: How to count with condition and restart again count

kongmanee — Mon, 06 Jun 2022 05:59:08 GMT

Re: How to count with condition and restart again count

AlB — Mon, 06 Jun 2022 07:04:39 GMT

kongmanee

It would help if you actually show the final result in the column you want to create

If you want it in a calculated column in the table you are showing:

NewCol = VAR date1_ = CALCULATE ( MAX ( Table1[Date] ), Table1[Date] < EARLIER ( Table1[Date] ), Table1[Value] = "NC", ALL ( Table1 ) ) VAR currentDate_ = Table1[Date] RETURN CALCULATE ( COUNT ( Table1[Column2] ), Table1[Value] = "C", Table1[Date] >= date1_, Table1[Date] <= currentDate_ )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: How to count with condition and restart again count

kongmanee — Mon, 06 Jun 2022 12:28:19 GMT

Thank you for your information.

I alread tried it, but it is not actually that I expected.

I need it by below example.

Re: How to count with condition and restart again count

AlB — Mon, 06 Jun 2022 13:08:21 GMT

kongmanee

Can you show the final desired result? I do not understand what you mean by "resetting", since all you show is 1, zeros and blanks. Please show a sample table with the expected result. And copy the data in a table here, rather than on a screen cap so that the contents can be copied

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: How to count with condition and restart again count

kongmanee — Mon, 06 Jun 2022 13:42:50 GMT

I am sorry I tried to put the table on the reply screen, but it shows error message. Could you tell me how to put a table on that.

Re: How to count with condition and restart again count

kongmanee — Mon, 06 Jun 2022 13:57:52 GMT

Date	Value	Column 2	desired result
01-Jun-22	C	1	0
02-Jun-22	C	1	0
03-Jun-22	C	1	0
04-Jun-22	C	1	0
05-Jun-22	N/A	0	0
06-Jun-22	C	1	0
07-Jun-22	N/A	0	0
08-Jun-22	NC	0	0
09-Jun-22	C	1	0
10-Jun-22	C	1	0
11-Jun-22	N/A	0	0
12-Jun-22	C	1	0
13-Jun-22	NC	0	0
14-Jun-22	C	1	1
15-Jun-22	C	1	1
16-Jun-22	N/A	0	0
17-Jun-22	C	1	1

Re: How to count with condition and restart again count

kongmanee — Mon, 06 Jun 2022 13:59:46 GMT

Date	Value	Column 2	desired result
1-Jun-22	C	1	0
2-Jun-22	C	1	0
3-Jun-22	C	1	0
4-Jun-22	C	1	0
5-Jun-22	N/A	0	0
6-Jun-22	C	1	0
7-Jun-22	N/A	0	0
8-Jun-22	NC	0	0
9-Jun-22	C	1	0
10-Jun-22	C	1	0
11-Jun-22	N/A	0	0
12-Jun-22	C	1	0
13-Jun-22	NC	0	0
14-Jun-22	C	1	1
15-Jun-22	C	1	1
16-Jun-22	N/A	0	0
17-Jun-22	C	1	1

Re: How to count with condition and restart again count

kongmanee — Mon, 06 Jun 2022 14:04:57 GMT

Re: How to count with condition and restart again count

AlB — Tue, 07 Jun 2022 07:02:55 GMT

kongmanee

I still do not understand the logic. Why is June 9th not a 1? You have and NC to rest on June 8th

Can you provide the expected result to the input data below?

Date	Value	Column 2
1-Jun-22	C	1
2-Jun-22	NC	0
3-Jun-22	C	1
4-Jun-22	C	1
5-Jun-22	N/A	0
6-Jun-22	C	1
7-Jun-22	N/A	0
8-Jun-22	NC	0
9-Jun-22	C	1
10-Jun-22	NC	0
11-Jun-22	N/A	0
12-Jun-22	C	1
13-Jun-22	NC	0
14-Jun-22	C	1
15-Jun-22	NC	0
16-Jun-22	N/A	0
17-Jun-22	C	1

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: How to count with condition and restart again count

kongmanee — Tue, 07 Jun 2022 08:40:02 GMT

I am sorry my expectation is below on the desired result column

Date	Value	Column 2	desired result
1-Jun-22	C	1	0
2-Jun-22	C	1	0
3-Jun-22	C	1	0
4-Jun-22	C	1	0
5-Jun-22	N/A	0	0
6-Jun-22	C	1	0
7-Jun-22	N/A	0	0
8-Jun-22	NC	0	0
9-Jun-22	C	1	0
10-Jun-22	C	1	0
11-Jun-22	N/A	0	0
12-Jun-22	C	1	0
13-Jun-22	NC	0	0
14-Jun-22	C	1	1
15-Jun-22	C	1	1
16-Jun-22	N/A	0	0
17-Jun-22	C	1	1

Re: How to count with condition and restart again count

AlB — Tue, 07 Jun 2022 08:40:46 GMT

kongmanee

This is not the input I posted

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.