counting entries at a particular time

[Anonymous]

Good morning

I have a table that spans a date period of 2 years, an example is given below.

The table records where an employee worked on a certain day.

I need to be able to search on the id of the employee and know on a selected date, how many times the employee had been to a certain location previously.

looking at that employee and others for a certain date, lets me the overall site experience the team had in that location.

In Excel I was able to do a countifs on each day which allowed me to count the days and put the answer in a column. I now wish to replicate that process in Dax. I have created a simplified table below which details the 3 calculated columns I want to create with the expected data in it.

How can I do this in Dax?

As an example I want to be able to see that on 7/10/23, that Allan had 3 days experience in London, Chris 0 in London, and John 7 in London, but all had 7 days company experience.

I look forward to hearing any advice

Lola

Name	UniqueID	Location	Date	Calc_London	Calc_Edinburgh	Calc_Sum
Allan	A1	London	01/10/2023	1	0	1
Allan	A1	London	02/10/2023	2	0	2
Allan	A1	Edinburgh	03/10/2023	2	1	3
Allan	A1	London	04/10/2023	3	1	4
Chris	A2	Edinburgh	01/10/2023	0	1	1
Chris	A2	Edinburgh	02/10/2023	0	2	2
Chris	A2	Edinburgh	03/10/2023	0	3	3
Chris	A2	Edinburgh	04/10/2023	0	4	4
John	A3	London	01/10/2023	1	0	1
John	A3	London	02/10/2023	2	0	2
John	A3	London	03/10/2023	3	0	3
John	A4	London	04/10/2023	4	0	4
Allan	A1	Edinburgh	05/10/2023	3	2	5
Allan	A1	Edinburgh	06/10/2023	3	3	6
Allan	A1	Edinburgh	07/10/2023	3	4	7
Allan	A1	London	08/10/2023	4	4	8
Chris	A2	Edinburgh	05/10/2023	0	5	5
Chris	A2	Edinburgh	06/10/2023	0	6	6
Chris	A2	Edinburgh	07/10/2023	0	7	7
Chris	A2	Edinburgh	08/10/2023	0	8	8
John	A3	London	05/10/2023	5	0	5
John	A3	London	06/10/2023	6	0	6
John	A3	London	07/10/2023	7	0	7
John	A4	London	08/10/2023	8	0	8

[some_bih]

Hi @Anonymous put measure in cal. column and check result

[some_bih]

Hi @Anonymous I created file from your data.

[Anonymous]

@some_bih 

Thank you for the response and the file. The fields that I have marked as Calc_ something are the fields that i want to be automatically calculated.

for example, I need the code for a column Calc_London that returns the number of times, the "uniqueID" has been in London at that "date"

I have been playing around with (countrows, table, table unique id, previous year etc)., but i cannot get the format to work.

If you have any clues that would be appreciated.

Lola

[some_bih]

Hi @Anonymous check v2 file.

In this file I calulated measure for London only(similar Ed.)

On tab 2 you have slicer to interact as you wish.

London as Measure =

CALCULATE(

    [#rows],

    'comm 02112023'[Location]="London"

)

[Anonymous]

@some_bih 

Thank you for your time and effort. I do not need this as a filter, I need this as a calculated column, so that the calculation is correct at that date for that individual. I will use this data to build a picture as i am looking at thousands of names and trips, which will not be selected as slicers. I am aware that my phrasing of this problem may not be helping here, so Ill try to recreate it differently to get the answer. I thank you for looking at this.

Lola