Search code examples
datefilterpowerbidaxdefault

Dax and Power BI, default and slicer

I am trying to set up a report for users which by default shows today´s data.

But I would also like people with whom the report is share to be able to pick data, from the seventies to year 2043.

I currently managed to set up a report filter in DAX :

Date_Today_Default = 
var currentrowdate = FORMAT('Date'[DATE], "dd/mm/yyyy")
var DateToday = FORMAT(TODAY(),"dd/mm/yyyy")
return
IF(DateToday = currentrowdate, "YES", "NO")

But I would like to give users the possibility to see default values when the report opens and slice the report as well. In this case, the users cannot change the date if "YES" is selected in my filter.

Is this doable in Power BI ?

Solution

  • You can leverage the ISFILTERED function to give you behavior like this.

    Start by creating a new table that you will use for the values in your slicer

    SlicerDate = SELECTCOLUMNS('Date', "Date",[Date])

    And then go into your model view and create a relationship between your SlicerDate table and your Date table on the Date field.

    Now make the following small changes to your dax. 

    Date_Today_Default = 
var currentrowdate = FORMAT(Max('Date'[Date]), "dd/mm/yyyy")
var DateToday = FORMAT(TODAY(),"dd/mm/yyyy")
return
IF (isFiltered(SlicerDate[Date]), "YES", IF (DateToday = currentrowdate, "YES", "NO"))

    Create a slicer visual and bring the date field from the SlicerDatetable into its values.

    When you choose a date in the slicer, you will cause 'Date_Today_Default' to automatically show 'yes' for all records, letting all of them through the filter you have on your visual. But then they will be filtered down by the date you just selected in the slicer! So that gives your users control over the date selected.

    When no date is chosen in the slicer, the 'Date_Today_Default' logic will fall into the IF DateToday = CurrentRow statement and only mark one row 'YES.' The filter you have on your visual will omit all the other records.

    enter image description here

    Hope it helps