cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JesperXpol
Frequent Visitor

Filter on added column

Hi all,

 

I know there are some issues with filtering on dates from SQL.

Therefore I am trying to filter on a added column:

AddColumns('[dbo].[Xpol B_V_$Sales Header]',
"Date int",
Value(Text('Order Date',"[$-nl]yyyymmdd")))


The add column is working. But when I put a Filter outside the addcolumn it isn't showing any data. 

 Filter(
AddColumns('[dbo].[Xpol B_V_$Sales Header]',
"Date int",
Value(Text('Order Date',"[$-nl]yyyymmdd"))),
'Date int'=Value(Text(Today(),"[$-nl]yyyymmdd")))

For another case  I worked on I asked our SQL programmer to add a Integer Date field. That worked.

But I don't want to add in every table in the database a integer date field.

 

Does anyone has a solution for this?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

Do you get errors and also do you have Today date records in the table?

if yes, i suggest you add another column in your SQL table as stated in the documentation:

Direct date filters do not work for SQL Server. However, you can create a calculated column that will work. For instance, ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) and then filter on the calculated number column.

 

Then use the new column to replace the Order date

 

Documentation:

https://docs.microsoft.com/en-us/connectors/sql/

View solution in original post

7 REPLIES 7
eka24
Super User III
Super User III

Try

Filter(AddColumns('[dbo].[Xpol B_V_$Sales Header]',
"Date int",Value(Text('Order Date',"[$-nl]yyyymmdd"))),'Date int'=Value(Text(Today(),"[$-nl]yyyymmdd")))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

 
JesperXpol
Frequent Visitor

Thanks for your reply, but I believe this is exactly the same as what I had.

I have copy paste your formula, however still not working.

 

If you have another solution, please let me know

FernandoTC
Resolver II
Resolver II

Hi @JesperXpol 

What I think is happening here is a delegation problem. Addcolumns only supports partial delegation. This means that the expression that defines the value of the new columns is delegable to the data source, but the output of the function Addcolumns is limited to the delegation output (max 2000 rows). 
This means that when you are using this expression:

 

 

 Filter(
         AddColumns(
                    '[dbo].[Xpol B_V_$Sales Header]',
                    "Date int",
                     Value(Text('Order Date',"[$-nl]yyyymmdd"))
                    ),
        'Date int'=Value(Text(Today(),"[$-nl]yyyymmdd"))
)

 

 

The data source that is being filtered by the function Filter consists on a table wit the first 2000 rows (or your established row limit) of the data source:'[dbo].[Xpol B_V_$Sales Header]' with the new columns added.

If you have more than 2000 records in your data source and neither of the first 2000 records date is today's date, you will receive an empty table as result.

I usually work in my Apps with SQL tables with above 10k records and I can filter by date directly without any issue. Just be careful for delegation issues.

If your data source has less than 2000 records then your problem may not come because of this, but you will have to take caution if your data grows above that limit because you will then face this issue.


What I recommend you:

  • Try date filtering directly (it has been improved several times).
  • Before any Addcolumns, try to filter the source as much as you can before using this funtion to avoid delegation problems.

 

Hope you find it helpful

FernandoTC
Resolver II
Resolver II

Hi @JesperXpol 

What I think is happening here is a delegation problem. Addcolumns only supports partial delegation. This means that the expression that defines the value of the new columns is delegable to the data source, but the output of the function Addcolumns is limited to the delegation output (max 2000 rows). 
This means that when you are using this expression:

 

 Filter(
         AddColumns(
                    '[dbo].[Xpol B_V_$Sales Header]',
                    "Date int",
                     Value(Text('Order Date',"[$-nl]yyyymmdd"))
                    ),
        'Date int'=Value(Text(Today(),"[$-nl]yyyymmdd"))
)

 

 

The data source that is being filtered by the function Filter consists on a table wit the first 2000 rows (or your established row limit) of the data source:'[dbo].[Xpol B_V_$Sales Header]' with the new columns added.

If you have more than 2000 records in your data source and neither of the first 2000 records date is today's date, you will receive an empty table as result.

I usually work in my Apps with SQL tables with above 10k records and I can filter by date directly without any issue. Just be careful for delegation issues.

If your data source has less than 2000 records then your problem may not come because of this, but you will have to take caution if your data grows above that limit because you will then face this issue.


What I recommend you:

  • Try date filtering directly (it has been improved several times).
  • Before any Addcolumns, try to filter the source as much as you can before using this funtion to avoid delegation problems.

 

Hope you find it helpful

Hi @JesperXpol 

What I think is happening here is a delegation problem. Addcolumns only supports partial delegation. This means that the expression that defines the value of the new columns is delegable to the data source, but the output of the function Addcolumns is limited to the delegation output (max 2000 rows).  This means that when you are using this expression:

 

Filter( AddColumns( '[dbo].[Xpol B_V_$Sales Header]', "Date int", Value(Text('Order Date',"[$-nl]yyyymmdd")) ), 'Date int'=Value(Text(Today(),"[$-nl]yyyymmdd")) ) 

 


The data source that is being filtered by the function Filter consists on a table wit the first 2000 rows (or your established row limit) of the data source:'[dbo].[Xpol B_V_$Sales Header]' with the new columns added.

If you have more than 2000 records in your data source and neither of the first 2000 records date is today's date, you will receive an empty table as result.

I usually work in my Apps with SQL tables with above 10k records and I can filter by date directly without any issue. Just be careful for delegation issues. If your data source has less than 2000 records then your problem may not come because of this, but you will have to take caution if your data grows above that limit because you will then face this issue.

What I recommend you:

  • Try date filtering directly (it has been improved several times).
  • Before any Addcolumns, try to filter the source as much as you can before using this funtion to avoid delegation problems.

Hope you find it helpful

Edit: To filter SQL by date you need to have this setting marked:

FernandoTC_1-1594724802384.png

 

I usally work with an Azure SQL database. Maybe with another SQL service you are not able to filter directly by date.

 

 

Do you get errors and also do you have Today date records in the table?

if yes, i suggest you add another column in your SQL table as stated in the documentation:

Direct date filters do not work for SQL Server. However, you can create a calculated column that will work. For instance, ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) and then filter on the calculated number column.

 

Then use the new column to replace the Order date

 

Documentation:

https://docs.microsoft.com/en-us/connectors/sql/

View solution in original post

JesperXpol
Frequent Visitor

Thanks. The documentation also says it will not work.

 

Only bad thing is that I need external programmer to add column in the database.

 

All thanks for the help.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,867)