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,
Suppose i have a dataset
Date Val Count
01/01/19 A 1
02/01/19 B 1
03/01/19 C 1
04/01/19 A 2
05/01/19 D 1
06/01/19 E 1
07/01/19 B 2
08/01/19 A 3
I would like to create a calcualted column (count) which counts the number of times a value (Val) appears in the dataset. The count needs to increment by 1 each time it comes across the same value.
Any help will be appreciated
Thanks
Solved! Go to Solution.
@Anonymous Please try this as a New Column.
Count = COUNTROWS(FILTER(ALL(Test06),[Date]<=EARLIER(Test06[Date]) && [Val] = EARLIER(Test06[Val])))
Proud to be a PBI Community Champion
@Anonymous Please try this as a New Column.
Count = COUNTROWS(FILTER(ALL(Test06),[Date]<=EARLIER(Test06[Date]) && [Val] = EARLIER(Test06[Val])))
Proud to be a PBI Community Champion
Hi @PattemManohar ,
Thank you for your solution. Suppose I didnt have the date column and just wanted to count based on the Val column purely. How would you modify the formula to do so?
Thank you
@Anonymous In that case, try adding an Index field and use that Index column instead of Date field. But if you don't want that too, considering having only [val] column then in that case, you can't get running total but can have grand total count against each val. that appeared in the whole dataset.
Proud to be a PBI Community Champion
Hello Sir,
I saw your reply on how to count those repeats and it does work; however, I have a table with 2.5 million rows and this approach take a very long time. Do you have another method that would achive the same reuslts but more efficient for larger data sets? ~Thank You
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |