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])),
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.