Forum Discussion

bencefarsang's avatar
bencefarsang
New Member
5 years ago
Solved

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

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!

Need Help
Tips and Tricks
  • AlB's avatar
    AlB
    5 years ago

    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 

     

5 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
      bencefarsang
      New Member
      5 years ago

      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's avatar
    negi007
    Community Champion
    5 years ago

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

  • dobregon's avatar
    dobregon
    Impactful Individual
    5 years ago

    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