Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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   |

 

Function:

 

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!

 

General Comment
Need Help

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    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 (
    DISTINCT (
        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,

    Cherry

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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 :)