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 2020M1...
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 

     

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!