Removing Blank Days from Power BI Chart

Question

I have created a weekly request measure like so :

RequestsWeekly = var result= CALCULATE(
DISTINCTCOUNTNOBLANK(SessionRequests[RequestDateTime]),
FILTER('Date','Date'[WeekDate]=SELECTEDVALUE('DateSelector'[WeekDate],MAX('DateSelector'[WeekDate]))))+0

RETURN 
    IF ( NOT ISBLANK ( result ), result)

DateSelector is a standalone table (not connected to any other table in data model) that I have created for all the dates for a dropdown menu select for a Power BI Dasbboard. Unfortunately as there are less dates in the Date Selector table than the Date table, I get ... Date table is the standard DATE table full of dates from 1970 to 2038. Date connects to Session Requests via a many to one relationships, single way filter. Session Requests is the main fact table.

I need to get rid of the blank row in my result set via DAX so it does not appear in my chart on the X axis. I have tried lots of different DAX combos like blank () and NOT ISBLANK. Do I need to create a table for the result set and then try to filter out the blank day there?

Ideally don't use images in your questions where text data will do (and be much smaller, easier to read, and possible to copy and paste out). — Dale K
If I where you I will use Filters pane and filter on the visual to exclude blank in report page — JonWay

Przemyslaw Remin Przemyslaw Remin · Accepted Answer · 2020-01-21T14:23:10

You should not check if the result is empty but if the VALUE ( Table[DayNameShort] ) exists for your current row context:

RequestsWeekly =
VAR result =
    CALCULATE (
        DISTINCTCOUNTNOBLANK ( SessionRequests[RequestDateTime] ),
        FILTER (
            'Date',
            'Date'[WeekDate]
                = SELECTEDVALUE (
                    'DateSelector'[WeekDate],
                    MAX ( 'DateSelector'[WeekDate] )
                )
        )
    ) + 0
RETURN
    IF (
        NOT ISBLANK (
            VALUE ( Table[DayNameShort] ) -- put here correct table name
        ),
        result
    )

Removing Blank Days from Power BI Chart

1 Answers