Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
kongmanee
Frequent Visitor

How to count with condition and restart again count

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.

12 REPLIES 12
AlB
Community Champion
Community Champion

@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

 

SU18_powerbi_badge

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.

 

kongmanee
Frequent Visitor

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

 

DateValueColumn 2desired result
1-Jun-22C10
2-Jun-22C10
3-Jun-22C10
4-Jun-22C10
5-Jun-22N/A00
6-Jun-22C10
7-Jun-22N/A00
8-Jun-22NC00
9-Jun-22C10
10-Jun-22C10
11-Jun-22N/A00
12-Jun-22C10
13-Jun-22NC00
14-Jun-22C11
15-Jun-22C11
16-Jun-22N/A00
17-Jun-22C11
AlB
Community Champion
Community Champion

@kongmanee 

This is not the input I posted


SU18_powerbi_badge

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.

 

AlB
Community Champion
Community Champion

@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 

 

SU18_powerbi_badge

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.

 

kongmanee
Frequent Visitor

DateValueColumn 2desired result
1-Jun-22C10
2-Jun-22C10
3-Jun-22C10
4-Jun-22C10
5-Jun-22N/A00
6-Jun-22C10
7-Jun-22N/A00
8-Jun-22NC00
9-Jun-22C10
10-Jun-22C10
11-Jun-22N/A00
12-Jun-22C10
13-Jun-22NC00
14-Jun-22C11
15-Jun-22C11
16-Jun-22N/A00
17-Jun-22C11
kongmanee
Frequent Visitor

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.

kongmanee_0-1654524286374.png

 

DateValueColumn 2desired result
01-Jun-22C10
02-Jun-22C10
03-Jun-22C10
04-Jun-22C10
05-Jun-22N/A00
06-Jun-22C10
07-Jun-22N/A00
08-Jun-22NC00
09-Jun-22C10
10-Jun-22C10
11-Jun-22N/A00
12-Jun-22C10
13-Jun-22NC00
14-Jun-22C11
15-Jun-22C11
16-Jun-22N/A00
17-Jun-22C11
AlB
Community Champion
Community Champion

Hi @kongmanee 

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

SU18_powerbi_badge

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.

 

kongmanee
Frequent Visitor

kongmanee_1-1654495136568.png

 

AlB
Community Champion
Community Champion

@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_
    )

SU18_powerbi_badge

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.

 

kongmanee
Frequent Visitor

Thank you for your information.

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

I need it by below example.

 

kongmanee_0-1654518239489.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.