CODEWITHSUNDEEP
sql-serverpowerbidax
James Khan
James Khan

Reputation: 841

Removing Blank Days from Power BI Chart

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.

enter image description here

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?

Upvotes: 1

Views: 2295

Answers (1)

Przemyslaw Remin
Przemyslaw Remin

Reputation: 6940

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
    )

Upvotes: 1

Related Questions