topic Re: Measure to countrows based on filtered results in DAX Commands and Tips

Measure to countrows based on filtered results

Regretti — Thu, 24 Feb 2022 00:17:05 GMT

Hello everyone,

I'm looking for some assistance on how to rewrite the following measure:

Monthly Required = SUMX(SUMMARIZE('Site Visit - Staff','Site Visit - Staff'[Last Name],'Site Visit - Staff'[First Name]

,'Required Site Visits'[Visits Per Month]),'Required Site Visits'[Visits Per Month])

This measure would count the number of visits required by supervisors and originally it was working how I wanted. However, as we have staff turnover or new staff, I need a way to keep a record of their visits as well. The Site Visit - Site Staff is a SharePoint list, and if I add a new person to the list, their name comes up from the beginning of the Power BI report even though they weren't our department, so I would like to filter that out.

My solution was to add an 'effective date' column and an 'end date' column to my list and display the only the visits that were completed in those dates. In essence, just filter out and count the visits required for the people that were active in between the start and end dates of their position.

I am truly struggling with how to filter this and am seeking any input that could point me in the right direction. Thank you in advance for any help you may be able to provide. This is my go to place for seeking solutions and i appreciate all of the help you provide everyone.

Re: Measure to countrows based on filtered results

edhans — Thu, 24 Feb 2022 02:42:36 GMT

If you want to filter them out of the data, then do this in Power Query before it loads the SharePoint list into the model. You'd need to post some sample data (mock itup in Excel for example) and show us what you are doing, but don't make your DAX overly complex because there is data there you don't want. Get rid of the data. That is Power Query's job.

A little bit of Power Query makes your DAX life a whole lot easier. 😁

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

Re: Measure to countrows based on filtered results

Regretti — Thu, 24 Feb 2022 03:00:05 GMT

Thank you!! Much appreciated.

Yes, that is what I'm going to do and that makes the most sense. I'd much rather straighten things out there and should have thought about that.

Re: Measure to countrows based on filtered results

Regretti — Sat, 26 Feb 2022 19:11:22 GMT

Okay, so I had a chance to try it out this week and it didn't get the results I was looking for. So, I'm guessing a little more explanation of the requirements on my end would be helpful.

I have a sharepoint list with the list of people, positions, and their start/end dates which I tie into the positions table for the amount of monthly visit required by that person bases on their position.

The way I have it currently, if I have a new person starting/or leaving, their required visits are displayed in my display table from the start of the year whether they started in May or whenever. I would like the slicer selection reflect on the amount of visits required based on who was in the position for that particular month.

name	position	start	end
bob	manager	2021-01-01	2100-01-02
pete	manager	2021-05-16	2100-01-02
sue	supervisor	2021-01-01	2100-01-02
frank	supervisor	2021-01-01	2100-01-02	position	visits required		table containing list of visits
jeff	president	2021-04-16	2100-01-02	president	1
paul	manager	2021-01-01	2100-01-02	manager	2
tim	manager	2021-01-01	2100-01-02	supervisor	4
steve	supervisor	2021-03-16	2100-01-02
sam	supervisor	2021-01-01	2100-01-02
stu	supervisor	2021-01-01	2100-01-02				Display table
beth	supervisor	2021-01-16	2100-01-02	month slicer		name	required visits	visits completed
jim	supervisor	2021-01-01	2100-01-02	jan
james	supervisor	2021-01-01	2100-01-02	feb
gord	supervisor	2021-01-16	2100-01-02	mar
may	supervisor	2021-01-01	2100-01-02	etc.
mary	supervisor	2021-01-01	2100-01-02