Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Not applicable

How to remove blank rows in a calculated table?

Hi. 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   | 




Ave. days to report = 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!


Resident Rockstar
Resident Rockstar

Hi @Anonymous,


If I understand your requirement correctly that you want to remove the blank rows of a calculated table.


You could refer to this formula below to create a calculated table.


Table = FILTER (
        SELECTCOLUMNS ( 'Planning',"team",'Planning'[Team], "Meeting", 'Planning'[Meeting], "report", 'Planning'[Report] )
    NOT ( ISBLANK ([report] ) )

The result of the calculated table is below.




Hope it can help you!


Best regards,


Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Not applicable

Interesting - thanks. Hadn't thought of creating a new table.


What does the DISTINCT do please? I'm new to DAX and can't see why you can't just use SELECTCOLUMNS.


As a rule, do you think it's better to define tables to produce the numbers one desires, or create measures?


Thanks 🙂

Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.