Filtering

[Anonymous]

Is it possible to have one filter in a report auto select the same results as another by only changing one value?

the example is that I have two seperate date filters in a report, from different date tables, yet they will always use the same selected value. So instead of needing to change each date filter, can one grab the results from the other?

[dilumd]

Hi @Anonymous

When you’re dealing with dates have a separate calendar table and related both of your tables to that calendar. and use the calendar table as the filter.

In a "blank query" copy and paste below code to populate a calendar table. you can do the required changes to the below code accordingly.

let
Source = List.Dates,
#"Invoked FunctionSource" = Source(#date(2015, 1, 1), 365*10, #duration(1, 0, 0, 0)),
#"Table from List" = Table.FromList(#"Invoked FunctionSource", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Index" = Table.AddIndexColumn(#"Table from List", "Index", 1, 1),
#"Renamed Columns" = Table.RenameColumns(#"Added Index",{{"Column1", "Date"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Number", each Date.Month([Date])),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Day", each Date.Day([Date])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Day Name", each Date.ToText([Date],"ddd")),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Month Name", each Date.ToText([Date],"MMM")),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom4",{"Date", "Index", "Year", "Month Number", "Month Name", "Day", "Day Name"}),
#"Added Custom5" = Table.AddColumn(#"Reordered Columns", "Quarter Number", each Date.QuarterOfYear([Date])),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom5", "Year", "Copy of Year"),
#"Renamed Columns1" = Table.RenameColumns(#"Duplicated Column",{{"Copy of Year", "Short Year"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Short Year", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type","Short Year",Splitter.SplitTextByRepeatedLengths(2),{"Short Year.1", "Short Year.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Short Year.1", Int64.Type}, {"Short Year.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Short Year.1"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"Short Year.2", "Short Year"}}),
#"Added Custom6" = Table.AddColumn(#"Renamed Columns2", "Quarter Year", each Number.ToText([Short Year]) & "Q" & Number.ToText([Quarter Number],"00")),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Custom6",{"Index", "Date", "Day", "Day Name", "Month Number", "Month Name", "Quarter Number", "Quarter Year", "Short Year", "Year"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns1",{{"Date", type date}, {"Day", Int64.Type}, {"Index", Int64.Type}, {"Month Number", Int64.Type}, {"Quarter Number", Int64.Type}, {"Month Name", type text}, {"Quarter Year", type text}, {"Year", Int64.Type}, {"Day Name", type text}, {"Short Year", type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Changed Type2", {{"Quarter Number", each "Q" & Text.From(_, "en-US"), type text}}),
    #"Added Custom7" = Table.AddColumn(#"Added Prefix", "MonthYear", each [Month Name] & [Short Year]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom7",{{"MonthYear", type text}})
in
#"Changed Type3"

[Anonymous · Accepted solution]

Yes, you can use like this:

Date2 = SELECTEDVALUE(Table1[Date1])

This way, date 2 will always refer what Date1 is filtered.

Thanks
Raj