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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

counting entries at a particular time

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

 

NameUniqueIDLocationDateCalc_LondonCalc_EdinburghCalc_Sum
AllanA1London01/10/2023101
AllanA1London02/10/2023202
AllanA1Edinburgh03/10/2023213
AllanA1London04/10/2023314
ChrisA2Edinburgh01/10/2023011
ChrisA2Edinburgh02/10/2023022
ChrisA2Edinburgh03/10/2023033
ChrisA2Edinburgh04/10/2023044
JohnA3London01/10/2023101
JohnA3London02/10/2023202
JohnA3London03/10/2023303
JohnA4London04/10/2023404
AllanA1Edinburgh05/10/2023325
AllanA1Edinburgh06/10/2023336
AllanA1Edinburgh07/10/2023347
AllanA1London08/10/2023448
ChrisA2Edinburgh05/10/2023055
ChrisA2Edinburgh06/10/2023066
ChrisA2Edinburgh07/10/2023077
ChrisA2Edinburgh08/10/2023088
JohnA3London05/10/2023505
JohnA3London06/10/2023606
JohnA3London07/10/2023707
JohnA4London08/10/2023808

 

5 REPLIES 5
some_bih
Super User
Super User

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






some_bih
Super User
Super User

Hi @Anonymous I created file from your data.

 

some_bih_0-1698922843117.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Anonymous
Not applicable

@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

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"
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Anonymous
Not applicable

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.