Forum Discussion
Dynamic MDX for Date Range
When you're in the query editor, you cannot reference a measure that has been created in DAX (just as you cannot reference a calculated column that has been created using DAX - that is a one-way-street!).
Assuming that the Sales table is already existent as another query, your formula would need to be written like this (in M):
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [DATE] >= List.Min(Sales[DATE]) and [DATE] <= List.Max(Sales[DATE])),
Hi, you can accomplish this by creating two measures, one for the max date from the Sales table, and one with the min date from the Sales table. You could then use these measures in your "Filtered Rows" formula.
MaxDate = MAX(Sales[Date]) MinDate = MIN(Sales[Date])
Thanks,
Sam Lester (MSFT)
SamLester I have tried your recomendation, but I dont think I am doing it correctly.
I create the following 2 measures:
SalesDateMax = MAX(SALES[DATE]) SalesDateMin = MIN(SALES[DATE])
and Updated the query to include these instead of the hard coded dates:
let
Source = Excel.Workbook(File.Contents(File_Dir & "\eXceler8\Projects\Excel Add-Ins\Clients\iRam Internal\Live files\iRAM_ADDIN_APP_01\SSF\SF\SFS\1PBI_DB\Support Tables\Calendar.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
#"Inserted Merged Column" = Table.AddColumn(#"Promoted Headers", "Year-Month", each Text.Combine({Text.From([Year], "en-US"), Text.From([Month_D], "en-US")}, "-"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Year-Month"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}, {"Day of Month", Int64.Type}, {"Day of Week ID", Int64.Type}, {"Day of Week", type text}, {"Week of Year ID", Int64.Type}, {"Week of Year", type text}, {"Month_ID", Int64.Type}, {"Month", type text}, {"Quarter ID", Int64.Type}, {"Quarter", type text}, {"Year", Int64.Type}, {"Week Year", type text}, {"Month Year", type date}, {"Quarter Year", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date", "DATE"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [DATE] >= SalesDateMin and [DATE] <= salesdateMax)
in
#"Filtered Rows"But I get the following error:
Expression error: The name 'SalesDateMin' wasn't recognized. Make sure it's spelled correctly.
When you're in the query editor, you cannot reference a measure that has been created in DAX (just as you cannot reference a calculated column that has been created using DAX - that is a one-way-street!).
Assuming that the Sales table is already existent as another query, your formula would need to be written like this (in M):
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [DATE] >= List.Min(Sales[DATE]) and [DATE] <= List.Max(Sales[DATE])),
Hi ImkeF , I am trying to accomplish this but it's not working. Here's my MDX
let
Source = PowerBI.Dataflows([]),
#"be94dad9-11a8-4fba-a077-08e5a9b3bcee" = Source{[workspaceId="be94dad9-11a8-4fba-a077-08e5a9b3bcee"]}[Data],
#"9dc22498-b3bc-470e-b655-ac419204634a" = #"be94dad9-11a8-4fba-a077-08e5a9b3bcee"{[dataflowId="9dc22498-b3bc-470e-b655-ac419204634a"]}[Data],
#"CEO Dashboard Outpatient Details1" = #"9dc22498-b3bc-470e-b655-ac419204634a"{[entity="CEO Dashboard Outpatient Details"]}[Data],
#"Filtered Rows" = Table.SelectRows(#"CEO Dashboard Outpatient Details1", each ([Visit_Group] = "OP Clinic Visit")),
#"Filtered Rows1" = Table.SelectRows(#"CEO Dashboard Outpatient Details1", each [Visit_Date] >= List.Min(DateDim[Date]) and [Visit_Date]<= List.Max(DateDim[Date])),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Visit_Group", "FiscalYear", "HAR", "BaseClass", "PatientClass", "ApptStatus", "VisitSubGroup", "ProviderName"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"VisitTime", type time}})
in
#"Changed Type"I don't get an error, but I also don't get filtered data. I've tried using Table.Min and Table.Max but that doesn't help. Appreciate any assistance!
Hi Anonymous ,
unfortunately I have no idea on this.