Hello,
I've just started to work a bit with Report Builder with our Power BI Report Server and although I've done some progress, I'm right now stuck with a requeriment which is so simple in Power Bi, but seems a bit more complex in a paginated report.
First I've created a KPI which is a simple COUNT with some filter expresions:
=CountDistinct ( IIF
( Fields!Diff.Value > 0 And Fields!Action_Phase.Value <> "Complete" And Fields!Action_Phase.Value <> "Void",Fields!Number.Value,Nothing),"DataSet1")
If I put this as an expression in a TextBox, works perfectly and gives me the expected result, say 150
However, now I have to create a Matrix like: City ; Count, but when I create this simple table and set the expresion also in the table, I obtain the following result:
City Count
A 150
B 150
C 150
When the expected result would be:
City Count
A 100
B 25
C 25
It seems the count is not being aggregated correctly.
I've also tried to create the count as a calculated field in the DataSet, and use this field in the table instead, but is not allowing me as it gives me the following error when I execute the report:
"The expression used for the calculated field 'Count' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions."
Any suggestions?
Thanks in advance!
