Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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.
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. |
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 |
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. |
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. |
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 |
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.
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 |
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. |
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. |
Thank you for your information.
I alread tried it, but it is not actually that I expected.
I need it by below example.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
25 | |
17 | |
12 | |
12 | |
10 |
User | Count |
---|---|
33 | |
27 | |
16 | |
14 | |
13 |