CODEWITHSUNDEEP
powerbipowerbi-desktop
DavidCHughes
DavidCHughes

Reputation: 25

Have a date slicer act as a single checkbox and a relative date

I have a Power BI Dashboard which has multiple visualisations on it (mock-up below) I want Charts 1&2 to be effected by the date slicer in order to show data from an individual month (dropdown list date slicer) but my third visualisation is a graph over time which currently is not affected by the slicer. However, I have had feedback that including moths beyond the month selected by the slicer is causing confusion. Is there any way to have a single select slicer show the data from before a point in time.

What My Dashboard Looks like

Upvotes: 1

Views: 351

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

It is possible, and you will need two things:

  1. Your chart X-Axis cannot use a column from the same table as the slicer.
    For example, your date slicer will most likely come from a Date table. You cannot use the same date table for your X-Axis. You could for example use the date column from your Fact table for the chart.

  2. You will need a measure to show values prior and upto the selected sliced date.
    An example measure could look like:
MyDateChartMeasure = 
  var maxDate = MAX('DateTable'[Date]) // max date value sliced on
  var result = CALCULATE(
    SUM('FactTaable[Value),
    ALL('DateTable'),  // ALL(...) removes any filters (ie the slicer)
    KEEPFILTERS('DateTable'[Date] <= maxDate) // only return dates upto the sliced date
  )
  return result

Upvotes: 1

Related Questions