Forum Discussion

cusdaiv's avatar
cusdaiv
New Member
10 years ago

Filter doesn't show complete data set

When I attempt to filter data (revenues by week), the filter stops at 2015-49 (data begins in 2014 and is ongoing). Is there a cap on the number of data points that I can sort individually? The points not shown in the filter are in my graph, but I would like to sort from september to the present and am currently unable to do so. 

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    cusdaiv - It looks like the way your filter column is configured, it ends up as text. You might consider creating a custom column that is essentially:

     

    = YEAR([date]) & MONTH([date])

     

    Then, you would have a format such as:

     

    201549

     

    Then, you could set your filter to be "greater than" 201549 for example.

     

    Tough to be sure I am answering your question correctly without sample data and such.

    • greggyb's avatar
      greggyb
      Resident Rockstar
      10 years ago

      Greg_Deckler, the concatenation operator will return a Text string, so inequality operators would sort lexicographically, and visual-, page-, and report-level filters would not have greater than / less than options.

       

      You could use the concatenation and then change the data type to Whole Number to get the filter options. Either way, simple concatenation is inappropriate for creating a year-month field, as there are 12 months. 20159 < 201412

       

      The best way to do this would be arithmetic or utilizing FORMAT():

       

      // DAX
// YearMonth arithmetic
YearMonth =
YEAR( DimDate[Date] ) * 100 + MONTH( DimDate[Date] )

// YearMonth with FORMAT()
// VALUE() is used to cast the Text string from FORMAT() to a 
      // Whole Number
YearMonth =
VALUE( FORMAT( DimDate[Date], "YYYYMM" ) )