How to remove empty rows with missing data from a calculated table chart?

[bencefarsang]

I've got the following table chart about sales data. The third and fourth columns are calculated by time intelligence (month-over-month and year-over-year changes). I haven't got any data from 2020M11 to 2020M12 yet, but they will arrive later.

Time intelligence calculates missing values as -100% changes for both MoM% and YoY% changes. This is clearly false result just because the data points are missing. Therefore, I would like to remove the rows. My question is: how can I remove these rows?

Thank you in advance!

[dobregon]

Hi @bencefarsang 

In your case , i would make that the 2 measures sales Mom and Sales Yoy depend of the measure sales. if the sales is blank, the measure should be blank

Sales Mom = 
Var A = your measure of sales Mom
RETURN
IF(ISBLANK(SALES),BLANK(),A)


Sales Yoy = 
Var A = your measure of sales Yoy
RETURN
IF(ISBLANK(SALES),BLANK(),A)

With this, if the measure sales doenst have values you will not see any values in the other % columns

Then you need to check if you have disabled the option 

by default i think it is disabled. if it is disabled you will not see the row if all the columns in that row have blank values. Hope this helps

[AlB · Accepted solution]

@bencefarsang 

Just follow the pattern shown earlier: 

Sales MoM% V2 =
VAR result_ =
    IF (
        ISFILTERED ( 'Table1'[DATE] );
        ERROR ( "Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column." );
        VAR __PREV_MONTH =
            CALCULATE (
                SUM ( 'Table1'[Sales] );
                DATEADD ( 'Table1'[DATE].[Date]; -1; MONTH )
            )
        RETURN
            DIVIDE ( SUM ( 'Table1'[Sales] ) - __PREV_MONTH; __PREV_MONTH )
    )
RETURN
    IF ( result_ = -1; result_ )

or if you want to do the check on Sales (using your current [Sales MoM%] measure

Sales MoM% V2 =
VAR result_ = [Sales]
RETURN
    IF ( NOT ISBLANK(result_); [Sales MoM%] )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @bencefarsang 

Just make a slight change to the  code of both measures. Calculate the result as you are doing now, and if the result is -100% (i.e. -1) the return a blank . For instance:

Sales MoM% =
VAR result_ = Here the code that you have now for this measure
RETURN
IF(result_ <> -1, result_)  

 Or, alternatively, you can check whether [Sales] is blank

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[bencefarsang]

Hello @AlB , I need some help with your snippet. The code is not working so far. Can you lend me a little help, please?

Sales MoM% = 
IF(
ISFILTERED('Table1'[DATE]);
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
VAR __PREV_MONTH =
CALCULATE(
SUM('Table1'[Sales]);
DATEADD('Table1'[DATE].[Date]; -1; MONTH)
)

RETURN
IF(__PREV_MONTH <> -1, DIVIDE(SUM('Table1'[Sales]) - __PREV_MONTH; __PREV_MONTH))
)

Original code:

Sales MoM% = 
IF(
ISFILTERED('Table1'[DATE]);
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
VAR __PREV_MONTH =
CALCULATE(
SUM('Table1'[Sales]);
DATEADD('Table1'[DATE].[Date]; -1; MONTH)
)

RETURN

DIVIDE(SUM('Table1'[Sales]) - __PREV_MONTH; __PREV_MONTH)
)

Thank you so much!

[negi007]

@bencefarsang  by right clicking and selecting the option exclude, you can hide values which you do not want to show in the visual.