How to remove blank rows in a calculated Power BI table?

1
votes

I have a days to report measure where I perform some calculation on each row for the numerator and then filter out blank rows for the denominator. Example table, code and result as follows:

Team |  Meeting    |  Report
aaa  | 1/1/2018    |  9/1/2018
aaa  | 1/1/2018    |  7/1/2018
bbb  | 1/1/2018    |  1/2/2018
bbb  | 1/1/2018    | 
ccc  | 1/1/2018    |  3/3/2018
aaa  | 1/1/2018    |

This is my function for the average days

CALCULATE(
        AVERAGEX(Planning,Planning[Report]-Planning[Meeting]),
        FILTER(Planning,NOT(ISBLANK(Planning[Report])))
    )

And I'd like:

Team | average
aaa  | 7 (14/2)
bbb  | 31 (31/1)
ccc  | 61 (61/1)

Function seems to work but I'm slightly paranoid about my (lack of) understanding of CALCULATE and FILTER than I may be doing something wrong?

1
powerbidax

1 Answers

2
votes

Your function looks fine. The FILTER removes any rows with a blank Report value and then the AVERAGEX evaluates for just those rows.

FYI, for this construction, you don't necessarily need FILTER you can just write the following since CALCULATE supports basic filtering:

Average = CALCULATE(AVERAGEX(Planning, Planning[Report] - Planning[Meeting]),
                    NOT(ISBLANK(Planning[Report])))

Another way to do this is to use FILTER inside of AVERAGEX instead of using CALCULATE:

Average = AVERAGEX(FILTER(Planning, NOT(ISBLANK(Planning[Report]))),
                   Planning[Report] - Planning[Meeting])