one page filter for two different dates

[tommer]

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?

[Kaviraj11]

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?

[tommer]

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]

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 !!

[tommer]

in which table do i create this measure? or is that not important?

for which table / which table do i specifiy in the isempty function?

i created this measure in a genericTable and specifiek the filtertable in the isempty function and dragged it to the filter pane and gave condition as 1. nothing happened.....

why do you suggest to check if the table is not empty?

[v-xinruzhu-msft]

Hi,

Thanks for the solution @Kaviraj11  offered and i want ot offer some more information for user to refer to.

hello @tommer , based on your descriotion, you can put  the year column, month column and quarter column to the page filter. and you only need to create the 1:n relationship(calendar date and other dates column in two tables).

Output

Or you can create three slicers, put the year , quarter, month to the slicers.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tommer]

problem still unsolved. reply incorrectly marked as solved.

[Kaviraj11]

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 !!

[tommer]

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....