Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MarkCBB
Helper V
Helper V

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 the dates dynamic based on another Table called "Sales" the column in the Sales Table is called "DATE".

I need this to take the Min and the Max date from the Sales table and filter the query above. 

 

Is this possible?

 

1 ACCEPTED SOLUTION

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

6 REPLIES 6
SamLester
Employee
Employee

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF Thank you - That did the trick

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.