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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Alienvolm
Helper IV
Helper IV

Filter table by field >= T-14

Hi, 

 

I have a dataset from a SQL DB that feeds a report and a couple of paginated reports. 

 

As a new requirement, for all reports I now only need to show the data where the field Phase2PlannedStartDate is equal or later than Today()-14 or blank. I have created a measure for T-14, but I haven't found a way of using that in the query editor to filter the column: 

Alienvolm_0-1625856825028.png

How do I apply this condition?

 

I'm not very familiar with M. I added this line to the advanced editor (the syntax check is okay):

 

 

 

#"Filtered by T-14" = Table.SelectRows([Phase2PlannedStartDate] >= "T-14" or [Phase2PlannedStartDate] = null)

 

 

I also tried this: 

#"Filtered by T-14" = Table.SelectRows([Phase2PlannedStartDate] >= (Date.AddDays(DateTime.LocalNow(), -14)) or [Phase2PlannedStartDate] = null)

 

but considering that T-14 as per today would be June 25, 2021 the query doesn't work:

 

Alienvolm_1-1625857359847.png

Any help with this would be greatly appreciated! 

 

~Alienvolm

1 ACCEPTED SOLUTION

Hi, @Alienvolm 

 

Sorry for my mistake, I wasn't clear on your final code until your last reply.
While I'm no expert on PowerQuery, one thing to note is that Let and in come in pairs.
Think of LET as the definition function and IN as the return value then it's clear.

 

I show the M code for my sample data as follows.

vangzhengmsft_0-1626312385157.png

In your case, i think the code will be this:

let
    Source = Sql.Databases("sql-shared-prod-us.database.windows.net"),
    #"JourneyAutomation " = Source{[Name="JourneyAutomation"]}[Data],
    dbo_JourneyUser = #"JourneyAutomation "{[Schema="dbo",Item="JourneyUser"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_JourneyUser,{{"TenantID", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"JourneyPathID", "isInActive"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"CurrentPhase", Text.Proper, type text}}),
    #"Filtered by T-14" = Table.SelectRows(#"Capitalized Each Word", each [Phase2PlannedStartDate] >= DateTime.LocalNow()-#duration(14,0,0,0)or [Phase2PlannedStartDate]=null)
in 
    #"Filtered by T-14"    //return the last result

Not sure if it's useful for you, here is my sample file

 

 

 

Best Regards,
Community Support Team _ Zeon Zheng

View solution in original post

10 REPLIES 10
CNENFRNL
Community Champion
Community Champion

#"Filtered by T-14" = Table.SelectRows(#"Name of Previous Step", let t=Date.From(DateTime.LocalNow()) in each [Phase2PlannedStartDate] >= t-#duration(14,0,0,0) or [Phase2PlannedStartDate] is null or [Phase2PlannedStartDate] = "")

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Hi @CNENFRNL

 

I tried your code and I got this error: 

 

Alienvolm_0-1625859620306.png

 

Here's the complete query code for your reference: 

let
    Source = Sql.Databases("sql-shared-prod-us.database.windows.net"),
    #"JourneyAutomation " = Source{[Name="JourneyAutomation"]}[Data],
    dbo_JourneyUser = #"JourneyAutomation "{[Schema="dbo",Item="JourneyUser"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_JourneyUser,{{"TenantID", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"JourneyPathID", "isInActive"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"CurrentPhase", Text.Proper, type text}})
in
    #"Capitalized Each Word"

 

Thanks in advance! 

 

Hi, @Alienvolm 

 

try this:

= Table.SelectRows(#"Previous Step", each [DateTime] >= DateTime.LocalNow()-#duration(14,0,0,0)or [DateTime]=null)

 result:

vangzhengmsft_0-1626157917508.png

 

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-angzheng-msft,

 

I tried that and it doesn't seem to work either:

Alienvolm_0-1626198384289.png

 

Also, after applying the change, I'm unable to view the table in Data view in Power BI Desktop (I can select the table but I see the data for a different table). 

 

This is the code with the modification (just in case I did something wrong:

 

let
    JourneyUser = let
    Source = Sql.Databases("sql-shared-prod-us.database.windows.net"),
    #"JourneyAutomation " = Source{[Name="JourneyAutomation"]}[Data],
    dbo_JourneyUser = #"JourneyAutomation "{[Schema="dbo",Item="JourneyUser"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_JourneyUser,{{"TenantID", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"JourneyPathID", "isInActive"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"CurrentPhase", Text.Proper, type text}}),
    #"Filtered by T-14" = Table.SelectRows(#"Capitalized Each Word", each [Phase2PlannedStartDate] >= DateTime.LocalNow()-#duration(14,0,0,0)or [Phase2PlannedStartDate]=null)
in
    #"Capitalized Each Word",
    #"Filtered Rows" = Table.SelectRows(JourneyUser, each true)
in
    #"Filtered Rows"

 

Thanks for your help! 

 

~Alienvolm

 

Hi, @Alienvolm 

 

Try to modify the last return value to the JourneyUser like below:

let
    JourneyUser = let
    Source = Sql.Databases("sql-shared-prod-us.database.windows.net"),
    #"JourneyAutomation " = Source{[Name="JourneyAutomation"]}[Data],
    dbo_JourneyUser = #"JourneyAutomation "{[Schema="dbo",Item="JourneyUser"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_JourneyUser,{{"TenantID", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"JourneyPathID", "isInActive"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"CurrentPhase", Text.Proper, type text}}),
    #"Filtered by T-14" = Table.SelectRows(#"Capitalized Each Word", each [Phase2PlannedStartDate] >= DateTime.LocalNow()-#duration(14,0,0,0)or [Phase2PlannedStartDate]=null)
in
    #"Capitalized Each Word",
    #"Filtered Rows" = Table.SelectRows(JourneyUser, each true)
in
    JourneyUser

 

 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, 

 

With this code:

let
    Source = Sql.Databases("sql-shared-prod-us.database.windows.net"),
    #"JourneyAutomation " = Source{[Name="JourneyAutomation"]}[Data],
    dbo_JourneyUser = #"JourneyAutomation "{[Schema="dbo",Item="JourneyUser"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_JourneyUser,{{"TenantID", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"JourneyPathID", "isInActive"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"CurrentPhase", Text.Proper, type text}}),
    #"Filtered by T-14" = Table.SelectRows(#"Capitalized Each Word", each [Phase2PlannedStartDate] >= DateTime.LocalNow()-#duration(14,0,0,0)or [Phase2PlannedStartDate]=null)
in 
    #"Capitalized Each Word",
    #"Filtered Rows" = Table.SelectRows(JourneyUser, each true)
in 
    JourneyUser

I get this: 

Alienvolm_1-1626264196984.png

The same is if I eliminate any of the two rows after the first "in".

 

If I eliminate the first "in" statement alltogether, the syntax is correct:

let
    Source = Sql.Databases("sql-shared-prod-us.database.windows.net"),
    #"JourneyAutomation " = Source{[Name="JourneyAutomation"]}[Data],
    dbo_JourneyUser = #"JourneyAutomation "{[Schema="dbo",Item="JourneyUser"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_JourneyUser,{{"TenantID", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"JourneyPathID", "isInActive"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"CurrentPhase", Text.Proper, type text}}),
    #"Filtered by T-14" = Table.SelectRows(#"Capitalized Each Word", each [Phase2PlannedStartDate] >= DateTime.LocalNow()-#duration(14,0,0,0)or [Phase2PlannedStartDate]=null)
in 
    JourneyUser

 

But then I get this:

Alienvolm_0-1626263637529.png

 

Hi, @Alienvolm 

 

Sorry for my mistake, I wasn't clear on your final code until your last reply.
While I'm no expert on PowerQuery, one thing to note is that Let and in come in pairs.
Think of LET as the definition function and IN as the return value then it's clear.

 

I show the M code for my sample data as follows.

vangzhengmsft_0-1626312385157.png

In your case, i think the code will be this:

let
    Source = Sql.Databases("sql-shared-prod-us.database.windows.net"),
    #"JourneyAutomation " = Source{[Name="JourneyAutomation"]}[Data],
    dbo_JourneyUser = #"JourneyAutomation "{[Schema="dbo",Item="JourneyUser"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(dbo_JourneyUser,{{"TenantID", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"JourneyPathID", "isInActive"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"id", type text}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type1",{{"CurrentPhase", Text.Proper, type text}}),
    #"Filtered by T-14" = Table.SelectRows(#"Capitalized Each Word", each [Phase2PlannedStartDate] >= DateTime.LocalNow()-#duration(14,0,0,0)or [Phase2PlannedStartDate]=null)
in 
    #"Filtered by T-14"    //return the last result

Not sure if it's useful for you, here is my sample file

 

 

 

Best Regards,
Community Support Team _ Zeon Zheng

Thank you @v-angzheng-msft!

 

That worked... and also thank you for the explanation on the M syntax. Do you know any resources that I could use for M queries in Power BI? I don't need to use it often, but it would be nice to have some beginners guidance. 

 

Thanks a lot! 

 

~Alienvom 

Thank you! 🙂

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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