How to count the ocurrence of a certain value in the latest N records of a column

[itpamp]

Hello,

I'm fairly new to Power BI and for the past month I haven't had the need to post a question here, but now I can't find a way to properly achieve what I need, hope any of you can help me.

I need, for each column in my table, to add a custom column beside it that counts the ocurrence of a certain value, but only considering N records of said column, not the whole thing.

I know it's not the same thing, but here's an example in Excel of what I wish to achieve in Power BI:

In this example, N_OF_WHITE would be the custom column containing the number of WHITE values in Example column in the same row and the two before. In particular, E7 (red text) is counting the amount of WHITE values in D7, D6 and D5 (highlighted records in Example column).

Thanks in advance for all your help.

[v-xiaoyan-msft]

Hi @itpamp ,

Are you trying to convert Excel formulas into calculated columns in Power BI? I'm afraid this is not currently possible.
In a calculated column, the context is clear - the current row and can be reduced to a column of the current row as needed.

If I have not understood your needs correctly, please do not hesitate to inform me.

Hope it helps,

Community Support Team _ Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[itpamp]

Oh, okay. Well, if not by using custom columns, is it possible by using measures?

What I need is a way to use Excel COUNTIF (or anything similar) in Power BI, or find a way to obtain a number (of times) a value is repeated in a certain column of my data.

Thanks again.

[v-xiaoyan-msft · Accepted solution]

Hi @itpamp ,

"COUNTIF" is a logical function that calculates a range of values based on a given condition. Power BI does not have any direct COUNTIF function, but can use a similar calculation.

Please check if following this procedure will meet your needs.

COUNTIF Function in Power BI 

Hope it helps,

Community Support Team _ Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[itpamp]

I haven't been able to make it work, but I think it's the best approach to solve my question.

Thanks!

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs/ILElVitVBZjnlJCZno7GCUlPQVGGwYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Color = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Color"}, {{"Count", each Table.RowCount(_), Int64.Type}}, 0)
in
    #"Grouped Rows"

[itpamp]

Hello @CNENFRNL , thanks for your reply.

Sadly I don't think that's what I'm looking for.

In your example, the Count column should have these values: 1, 1, 1 and 1.

I know I didn't explained that clear, but now I edited the first post in an effort to explain it better.