Power BI Report Builder Count IF Expression

Question

I'm trying to create a count if calculation in powerbi report builder, previously I used the following logic to do this in power bi desktop but this does not transfer across to report builder;

MthMinus1LicStaffCnt>10 = CALCULATE (
COUNTROWS( 'Query1' ),
FILTER ( Query1, Query1[MthMinus1LicStaffCnt] >=10)
)
    / 
  COUNTROWS ( Query1 )

In report builder I have used variations of the following expression;

=IIf(Fields!MthMinus1LicStaffCnt.Value >= "10", Sum(Fields!MthMinus1LicStaffCnt.Value), 0 )

But it generates the following error:

The expression used for the calculated field 'test' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions.

Any ideas how to implement this in report builder, the basic logic is to count a field if it is >= 10

Thanks Blowers

Hannover Fist Hannover Fist · Accepted Answer · 2021-03-03T17:41:50

I'm a little fuzzy on the Power BI function but I think you just need to put your aggreagate around the IIF:

=SUM(IIf(Fields!MthMinus1LicStaffCnt.Value >= "10", Fields!MthMinus1LicStaffCnt.Value, 0))

This will check each row and return the value if it's over 10 or zero if it's not and then sums them all together.

Previously the IIF was checking one row and then getting the sum of all.

Power BI Report Builder Count IF Expression

1 Answers