CODEWITHSUNDEEP
powerbi
Rider03
Rider03

Reputation: 11

In Power BI dax, please help me out to count the total occurrences on consecutive number in "n" times

I would like to count in Power BI the total counts when a value comes out in 3 consecutive order. Highly appreciated the support.

Upvotes: 0

Views: 154

Answers (1)

abbasador
abbasador

Reputation: 111

If I may denote your columns as "tme" and "val" and the table as "tbl": a two-step approach with a calculated column would look as follows:

First, we evaluate the time-difference as a calculated column in every row to the next-largest zero with time before the time of the respective row (see "EARLIER"). We further take the modulus with respect to 3, so that we expose all entries, which are 3, 6, 9 elements greater than the next-smaller zero.

__DiffToLargestZeroBeforeMod3 = MOD(tbl[tme] - CALCULATE(
    MAX(tbl[tme]),
    FILTER(tbl, tbl[val] = 0),
    tbl[tme] < EARLIER(tbl[tme])
), 3)

Next, we define a measure, which counts all '1' entries that have 3-modulo 0

_CntTriplets = CALCULATE(
    COUNT(tbl[val]),
    tbl[__DiffToLargestZeroBeforeMod3] = 0,
    tbl[val] = 1
)

Hope this does the job for you! Note, that this also works if the column were to start with three 1's, as the result from the MAX operator would be None and, e.g. 3-None = 3 for DAX. Also note, that the calculated column is probably not the most compute-efficient approach ;)

Upvotes: 1

Related Questions