COUNTIFS and moving ranges in column

[KarolSJZ]

Hello,

I am wondering how to write a formula for a column in a table (not measure) in PowerBI that will work by moving ranges.

Below is an example of an implementation using COUNTIFS in Excel. I think the screenshots say it all and I don't need to describe anything else. For clarity, LICZ.WARUNKI = COUNTIFS in Excel.

Thank you in advance for your help.

Karol

[v-shex-msft · Accepted solution]

Hi @KarolSJZ,

>>If I understood correctly, it is not possible to create such a column with data?

Nope, calculate column also suitable for this processing. (notice: calculate column/table not able to dynamic interaction with filter/slicer effects)

You can add an index column to your table, then you can try to use the 'earlier' function to use the current value and current index as conditions to looping and calculate through the table records.

How To Use The EARLIER Function In Power BI - A DAX Tutorial | Enterprise DNA

Regards,
Xiaoxin Sheng

[KarolSJZ]

@v-shex-msftthank you for your response.

If I understood correctly, it is not possible to create such a column with data? Unfortunately, using measures, I will not be able to use relationships between tables.
Is there any other way out?

[v-shex-msft · Accepted solution]

Hi @KarolSJZ,

>>If I understood correctly, it is not possible to create such a column with data?

Nope, calculate column also suitable for this processing. (notice: calculate column/table not able to dynamic interaction with filter/slicer effects)

You can add an index column to your table, then you can try to use the 'earlier' function to use the current value and current index as conditions to looping and calculate through the table records.

How To Use The EARLIER Function In Power BI - A DAX Tutorial | Enterprise DNA

Regards,
Xiaoxin Sheng

[KarolSJZ · Accepted solution]

Thank you, that helps me a lot to figure out and solve the problem.

I created index column with measure found on this topic.

https://community.powerbi.com/t5/Desktop/Add-calculated-index-column-by-DAX/td-p/72448

index = 
RANKX (
    FILTER (
        yourTable,
        EARLIER ( yourTable[CC] ) = yourTable[CC]
            && EARLIER ( yourTable[Type] ) = yourTable[Type]
            && yourTable[Cluster] = yourTable[Cluster]
            && EARLIER ( yourTable[Status] ) = yourTable[Status]
    ),
    yourTable[Avg-Position],
    ,
    ASC
  

[v-shex-msft]

Hi @KarolSJZ,

AFAIK, countifs function in excel means the count calculation with filters, you can use the iterator function 'countx' instead.
Power bi data model tables do not include column and row index, you need to use filter functions and conditions to choose specific ranges or records.

measure =
VAR curr =
    MAX ( Table[Index] )
RETURN
    COUNTX (
        FILTER (
            ALLSELECTED ( Table ),
            [Field1] = "conditon1"
                && [Field2] = "condition2"
                && [Index] <= curr
        ),
        [Column]
    )

Notice: Recursion calculations not supported in DAX functions.

Regards,

Xiaoxin Sheng