Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
Yes, you can use like this:
Date2 = SELECTEDVALUE(Table1[Date1])
This way, date 2 will always refer what Date1 is filtered.
Thanks
Raj
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"
Yes, you can use like this:
Date2 = SELECTEDVALUE(Table1[Date1])
This way, date 2 will always refer what Date1 is filtered.
Thanks
Raj
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |