Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

slicer to split data in half

I have a single table source.  This table has a Date attribute.  This Date spans a 6-month range.  I have created a derived column in my PBI data model to identify if the given date is in the first h...
Need Help
danextian's avatar
danextian
Super User
6 years ago

Hi Anonymous,

 

Without a sample data, I can only imagine how it looks. 

There are many ways to achieve your requirement and this just one.

  • Create this calculated column in your table which will return the month order starting from the earliest date. 
Rolling Month Number =
DATEDIFF (
    CALCULATE ( MIN ( 'Table'[Date] ), ALL ( 'Table' ) ),
    'Table'[Date],
    MONTH
) + 1
  • Create this calculated table. This should be a disconnected table and should not have a relationship with your fact table.
    • Range =
DATATABLE (
    "Range", STRING,
    "MaxMonth", INTEGER,
    {
        { "first 3 months", 3 },
        { "all six months", 6 }
    }
)
​

     

  • Use this measure to as a visual filter.  From the filters pane, select is not blank. 

    • RowCount = 
CALCULATE (
    COUNTROWS ( 'Table' ), 
    FILTER ( 'Table', 'Table'[Rolling Month Number] <= MAX ( Range[MaxMonth] ) )
)
  • Use the Range column from the calculated table Range in a slicer. If "first 3 months" is selected, the RowCount measure will return blank for rolling month numbers 4-6 and will not be visible in your visual. If not a single item is selected, the above measure will not filter any rows. If you don't want to show any data if no item is selected, you may change <= MAX ( Range[MaxMonth] ) ) to <= SELECTEDVALUE ( Range[MaxMonth] ) ).