Incremental Value Increase by Row based on True/False of other column

[Anonymous]

Hi Power BI Community!

As a new user, and I have reached a wall and am now seeking guidance from y'all.

Problem: I am trying to increment values (i.e. 1, 2, 3, etc.) for specific rows in Column B depending on True/False in Column A. If the value is False in Column A, I want the next True value to continue to follow the incremental increase. Please see the example of the output in Column B*** for your reference. Grateful for any help on this!

Column A          Column B***

True                       1

True                       2

False                   (blank)

True                       3

True                       4

False                    (blank)

False                    (blank)
True                       5

True                       6

[Zubair_Muhammad · Accepted solution]

@Anonymous 

I believe it is much easier to do this in Power Query by adding Index Columns

Please see the file attached with your data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCikqTVWK1UFiuCXmFKMJYZHDqSoWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type logical}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Column1", Order.Descending}}),
    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index.1", 1, 1),
    #"Sorted Rows1" = Table.Sort(#"Added Index1",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Index"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if [Column1] =true then [Index.1] else null),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index.1"})
in
    #"Removed Columns1"

[Anonymous]

Thanks so much @Zubair_Muhammad  and @Anonymous  for your assistance.

Zubair's option looks great!

Many thanks for this - solved my problem. Looking forward to keep learning this.

Best

K

[Anonymous]

@Anonymous  - Do you want to order by anything, or is the counting random?

I created the following Calculated Table with DAX:

Rand True False = ADDCOLUMNS(GENERATESERIES(1,100,1),"Random Truth", IF(RAND()>.5,TRUE(),FALSE()))

Then, the following Calculated Column:

True Ranking = 
var truth_current_row = [Random Truth]
var rownum = [Value]
return 
COUNTROWS(
    FILTER(
        ALL('Rand True False'),
        [Value] <= rownum           //Count rows less than or equal to the current row.
        && [Random Truth] = TRUE() //Only count TRUE rows.
        && truth_current_row = TRUE() //Only allows TRUE rows to receive a value.
    )
)

Cheers!

Nathan