Assigned to Previous column

[sivasrao]

The below table has two columns. 

Event	Target
C	0
C	0
C	3
R	0
R	0
R	0
C	0
C	2
R	0
R	0

My requirement is Target Column.

Target: 

The count of R values will be assigned to the previous Cell or Record of the first R.

In this example, R will start from the 4th cell up to the 6th cell. The total Count is 3.

The value 3 will assign to the 3rd cell (  before the cell where the first R appears)

Same as the 9th and 10th having R, the count is 2. That has been assigned to the 8th cell.

I hope you understand the requirement.

[sivasrao]

@amitchandak @Greg_Deckler 

pls, go through this problem.

  Thank you.

[johnyip]

@sivasrao , I cannot quite figure out the PowerBI way right now, but I can figure that out using Excel formula.

The following are the data I mimicked using MS Excel.

The forumlas are as below:

A
B	=AND(A2="R",A3="R")
C	=IF(AND(A2="R",A3<>"R"),TRUE,FALSE)
D	=IF(OR(B2=TRUE,C2=TRUE),1,0)
E	=IF(AND(B2=FALSE,C2=FALSE,OR(AND(B3=TRUE,C3=FALSE),AND(B3=FALSE,C3=TRUE))),SUM(D2:INDIRECT("D"&MATCH(TRUE,C2:C$1048576,0)+ROW(D2)-1)),0)

Just prepare your column A in Excel format, with the headers in row 1. Paste the formulas in B1 to E1 and then drag then down to your end of the data.

Finally, load column A and column E in the PowerBI.

[sivasrao]

By using    =IF(A2="C",0,1) we can get the D column.

Can you please suggest a logic if we have only two columns like C, R in one column and O,1 in another column.

See the below table,

Event	Binary Help
C	0
C	0
C	0
R	1
R	1
R	1
C	0
C	0
R	1
R	1

[johnyip]

In this case, just merge the logic and B2 and C2 into E2. This will make the formula hard to read though, although it will create fewer columns.

And yes, you can get column D as easy as that, my mind's screwed up when dealing with the logics.

[sivasrao]

If we have only two columns as I mentioned in the above reply,then what logic we can put to get the Target Column. 

Please give that logic. 

[sivasrao]

Thank you for the reply.

I will go through it. 

[johnyip]

@sivasrao , will you display the "requirement" in vis of Power BI (like table vis), or is it the requirement of your source data you wish to prepare, where you only have the event column and need to add that target column in it?

[sivasrao]

Thank you for your quick reply.

 It is from my source data. These 2 columns are important in my table to satisfy this requirement.

Need the count of R values and should appear before the row or cell where the first R appears only.

The remaining R values and C values are 0.

[johnyip]

Do you have some columns like [ID] in your data, so your data (table) is always displayed like the way you show? And from your requirement, for this moment I believe preparing your data in Excel and then load it in PowerBI is the easiest way.

[sivasrao]

My data having only 1st column. I want to get 2nd column by using 1st in Power BI. Otherwise we can create one more dummy column (or) Index column for support to get the Target.