Assigned to Previous column

Question

The below table has two columns.&nbsp;&nbsp;Event&nbsp;TargetC0C0C3R0R0R0C0C2R0R0&nbsp;My requirement is Target Column.Target:&nbsp;The count of R values will be assigned to the previous Cell or Record of the first R.&nbsp;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 (&nbsp; before the cell where the first R appears)&nbsp;Same as the 9th and 10th having R, the count is 2. That has been assigned to the 8th cell.&nbsp;I hope you understand the requirement.&nbsp; &nbsp;&nbsp;

johnyip · Answer

sivasrao&nbsp;, 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 · Answer

Thank you for your quick reply.&nbsp;It is from my source data. These 2 columns are important in my table to satisfy this requirement.&nbsp;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.&nbsp;&nbsp;

sivasrao · Answer

amitchandak&nbsp;Greg_Deckler&nbsp;pls, go through this problem.&nbsp; Thank you.

johnyip · Answer

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 · Answer

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.&nbsp;

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)

Forum Discussion

Assigned to Previous column

10 Replies

Related content

Calculated column to assign numeric code to dates

Assigning a calculated column as field value

Referring Same Column - Previous Value

Assign Column Value to a Variable

Priority Based Sales Rep Assignment

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