Forum Discussion

MarkCBB's avatar
MarkCBB
Helper V
10 years ago
Solved

Dynamic MDX for Date Range

Hello,   I have the following line in my query: #"Filtered Rows" = Table.SelectRows(#"Inserted Quarter", each [Date] >= #date(2014, 1, 1) and [Date] <= #date(2016, 5, 31)) I would like to make th...
Need Help
  • ImkeF's avatar
    ImkeF
    10 years ago

    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])),
ImkeF's avatar
ImkeF
Community Champion
10 years ago

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])),
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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!

  • ImkeF's avatar
    ImkeF
    Community Champion
    7 years ago

    Hi Anonymous ,

    unfortunately I have no idea on this.