Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

one page filter for two different dates

I have 2 visualisations on the same page.

these visualisations are based on 2 different tables / each visualisation is based on its own, specific table. 

both visualisations contain a date field/column.

 

I would like to have one page filter that applies to both the visualisations.

so the user navigates to this page in Power BI Service / browser and chooses Filters \ Page Filter \ SomeDateFilter \ SomeValue and SomeValue applies to both visualisations.

 

any advice how to do this as simple as possible?

 

Need Help

8 Replies

  • Kaviraj11's avatar
    Kaviraj11
    Solution Sage
    2 years ago

    Hi,

     

    I would suggest you to create Date/Calendar Table and there are multiple ways to create them. 

    Create date tables in Power BI Desktop - Power BI | Microsoft Learn

     

    Here’s a simple DAX formula to create a basic date table:

    DateTable = CALENDAR(DATE(2023, 1, 1), DATE(2023, 12, 31))

    For a more advanced date table that includes additional columns like month names, day of the week, and so forth, you can use the following template:

    Date = 
VAR MinYear = YEAR(MIN(Sales[Order Date]))
VAR MaxYear = YEAR(MAX(Sales[Order Date]))
RETURN
ADDCOLUMNS(
    FILTER(
        CALENDARAUTO(),
        AND(
            YEAR([Date]) >= MinYear,
            YEAR([Date]) <= MaxYear
        )
    ),
    "Calendar Year", "CY " & YEAR([Date]),
    "Month Name", FORMAT([Date], "mmmm"),
    "Month Number", MONTH([Date])
)

     

    Then you can create a relationship as Many to One from both the tables using Date Columns to Date Table. Additionally, you will use the date column from Date Table in a Slicer. It will display the output as you expect.

     

    I hope I understood the request correctly.  Let me know if you any questions.

     

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      hi Kaviraj,
      thanks for your suggestion.
      I created a datefiltertable.
      the datefiltertable contains a date-field.
      the date-field contains all the day between a min and a max using CALENDAR().

       

      I related this date-field in my model to the date-field of my visualisation's table.
      but my visualisation shows year, quarter and month.

       

      I can create a year-field, a quarter-field and a month-field in my datefiltertable, but I seem to have 2 problems:

      1. how to avoid multiple year, quarter and month combinations in my filtertable?

      2. how to relate these filtertable year-field, quarter-field and month-fields in my model to fields of my visualisation's table since only max 1 many-to-many relation is allowed between 2 tables....

  • Kaviraj11's avatar
    Kaviraj11
    Solution Sage
    2 years ago

    Hi,

     

    For 1st Problem, you will need to unselect the hierarchy date. Select the highlighted one.

     

    I have a trouble understanding the second question. Could you share an example or a pbix file?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      For 1st problem, I unselected the 'hierarchy date'-option by selecting the 'date'-option.

      still, my table contains values / records for every day.

      I want the user to only see a few years and corresponding quarters and months in the filter.

      not every day / not all the days in the month....

      • Kaviraj11's avatar
        Kaviraj11
        Solution Sage
        2 years ago

        Create this measure:

         

        SlicerFilter = INT ( NOT ISEMPTY ( 'Table Name' ) )

         

        and drag it to the filter pane and give condition as 1

         

         

        Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!