Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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"
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 - 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |