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

Filter datatable from current month and current user

I want to filter the DataTable from Sharepoint to get only the data for the current month and the current user. 

 

Here is what I have

 

 

Filter('Table Name', Date = Date(Year(Today()), Month(Today()), 1) && User().FullName ='Created By'.DisplayName)

 

 Screenshot 2021-01-04 133534.jpg

 

If I do one condition at a time, the table populates. But it does not work with 2 conditions. 

1 ACCEPTED SOLUTION

Accepted Solutions
cf_amart
Frequent Visitor

Here is what I did to make it work.

 

Created a label with Items = User().FullName.

 

Then in the Filter function of the data table, after the date = Date(...), Label.Text = 'Created By'.DisplayName

 

View solution in original post

7 REPLIES 7
gabibalaban
Super User II
Super User II

Hi @cf_amart ,

There doesn't seem to be anything wrong with your formula, except for delegation issues. Are you sure that there are items in the list that simultaneously meet those conditions?

Regarding the delegation issues, try to define context variable, like this:

UpdateContext({myDate:Date(Year(Today()),Month(Today(),1)});

UpdateContext({userName:User().FullName})

and use it variable inside your formula:

Filter(yourList,Date=myDate && userName='Created By'.DisplayName)

 

Hope it helps !

 

cf_amart
Frequent Visitor

The delegation error is saying "the formula might not work correctly on large data sets". Yes, I myself have entered data for this current month, so it should be showing some rows. 

FabianAckeret
Super User
Super User

Hi @cf_amart 

 

Your condition is checking whether you have some data entered on the FIRST of the current month. So that would be the 1st of January. Can you check if this is true?

 

Other than that, I would also recommend you to not check against a display name. A better solution would be to filter for user Principal Names. Or Claims, if you're working with SharePoint. However, that is not the reason why no data is being shown.

 

I assume it might be a case sensitive issue. Try the following:

 

Filter(
   'Table Name', 
   Date = Date(Year(Today()), Month(Today()), 1),
   Lower(User().FullName) = Lower('Created By'.DisplayName)
)

 

Kind regards,

Fabian

 


Please click Accept as Solution if my post answered your question. Like my answer? Consider giving it a Thumbs Up. Others seeking the same answers will be happy you did.

Filter('Inventory Entries Hobbs', Date = Date(Year(Today()), Month(Today()), 1), Upper(Office365Users.MyProfile().DisplayName) = Upper('Created By'.DisplayName))

Correct, all entries are made on the 1st of each month (I have that fixed in my DatePicker already). 

 

I tried the upper and lower for case sensitive, and the datatable is still empty.  I also tried using the Office365Users function instead. 

 

 

 

cf_amart
Frequent Visitor

If I hardcode in a name (mine or other users), the table works perfectly with the date filter. 

Filter('Inventory Entries Hobbs', Date = Date(Year(Today()), Month(Today()), 1), "My Name" = 'Created By'.DisplayName)
cf_amart
Frequent Visitor

Here is what I did to make it work.

 

Created a label with Items = User().FullName.

 

Then in the Filter function of the data table, after the date = Date(...), Label.Text = 'Created By'.DisplayName

 

View solution in original post

FabianAckeret
Super User
Super User

@cf_amart 

 

Is SharePoint your data source?

Either way, I would always recommend to use the value from the data source BEFORE the value you're trying to match against.

 

E.g.:

 

Filter(
   'Table Name', 
   Date = Date(Year(Today()), Month(Today()), 1),
   Lower('Created By'.DisplayName) = Lower(User().FullName)
)

 

 

and to make it bulletproof and definitely sort out ambiguity, you could do this:

 

Filter(
   'Table Name' As tempTable, 
   tempTable.Date = Date(Year(Today()), Month(Today()), 1),
   Lower(tempTable.'Created By'.DisplayName) = Lower(User().FullName)
)

 

I hope this helps.

 


Please click Accept as Solution if my post answered your question. Like my answer? Consider giving it a Thumbs Up. Others seeking the same answers will be happy you did.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,500)