Forum Discussion

SemaD_'s avatar
SemaD_
Regular Visitor
3 years ago
Solved

Not able show dates in my visual without data

I have a line graph visual that uses my mannually created calendar table (Hierarchy) and Data from another Table to show the average sessions over the period. However, as you can see when you drill d...
Need Help
Tips and Tricks
  • SemaD_'s avatar
    SemaD_
    3 years ago

    I have found a solution DAX to show any blanks as 0!

    AverageWithoutZero =
    VAR CalculatedAverage =
    AVERAGEX(
        SUMMARIZE(
            'Pogo E Connect Data',
            'Calendar'[Year],
            'Calendar'[Quarter]
        ),
        CALCULATE(
            AVERAGEX(
                CALCULATETABLE(
                    SUMMARIZE(
                        'Pogo E Connect Data',
                        'Calendar'[Month],
                        'Pogo E Connect Data'[Charger Type]
                    ),
                    CROSSFILTER('Pogo E Connect Data'[Start Date], 'Calendar'[Date], BOTH)
                ),
                CALCULATE(
                    AVERAGEX(
                        VALUES('Calendar'[Date]),
                        COALESCE([SDR ID average per CP ID], 0)
                    )
                )
            )
        )
    )
    RETURN
    IF (
        ISBLANK(CalculatedAverage),
        0,
        CalculatedAverage
    )

Anonymous's avatar
Anonymous
Not applicable
3 years ago

have you tried adding 

 

Average Number of Sessions = AVERAGEX( VALUES('Calendar'[Date]), CALCULATE( AVERAGEX( SUMMARIZE(

SemaD_'s avatar
SemaD_
Regular Visitor
3 years ago

I tried your suggestion but unfortunately the results of the visual still excludes dates without data😣