Forum Discussion

ajames95's avatar
ajames95
Frequent Visitor
7 years ago
Solved

How to include null values in filter visualization?

Hi,    I have a simple graph that i am filtering with a date slicer.  However I want the null values to show when i click on certain dates?    This is when I there is no filtering on the graph. ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi ajames95 

    Leave main data table and calendar table no relationship

    Create a measure

    Measure =
VAR slectedmo =
    SELECTEDVALUE ( 'calendar'[year-month] )
VAR y_m =
    FORMAT ( MAX ( 'Table'[date] ), "yyyy-mm" )
RETURN
    CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'Table', slectedmo = y_m ) )

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi ajames95 

Leave main data table and calendar table no relationship

Create a measure

Measure =
VAR slectedmo =
    SELECTEDVALUE ( 'calendar'[year-month] )
VAR y_m =
    FORMAT ( MAX ( 'Table'[date] ), "yyyy-mm" )
RETURN
    CALCULATE ( SUM ( 'Table'[value] ), FILTER ( 'Table', slectedmo = y_m ) )

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • ajames95's avatar
    ajames95
    Frequent Visitor
    7 years ago

    Hi Maggie, 

     

    I only have one table - 

     

    1 Year/Month = FORMAT('Table'[Logged Date],"YYYY-MM")

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      7 years ago

      Hi ajames95 

      There is no direct way to slove your problem, so i provide a workaround.

      Please create a calendar table:

      New Table-> enter this formula

      calendar = ADDCOLUMNS(CALENDARAUTO(),"year-month",FORMAT([Date],"yyyy-mm"))

      Then follow my methods in the previous post.

       

      Best Regards
      Maggie

       

      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.