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
AnneC
Advocate I
Advocate I

Counting Same Data that Occurs over Multiple Years

Hi Everyone

 

I have a problem, and any help would be appreciated.  In our organisation individuals can participate once or multiple times within a year, or alternatively, not at all.  Each individual has a unique identifier.

 

I am not interested in how many times an individual has participated over a year, just whether they did particpate, but I need to do this over multiple years and they need to be consecutive, so if my data looked like this:

YearUnique Identifier
2016A
2016B
2016B
2016D
2016E
2017A
2017B
2017C
2017C
2017D
2017E
2018A
2018B
2018C
2018D
2018D
2018D
2018D
2019A
2019A
2019B
2019D
2019E

 

The result for 2016-2019 would be:

Unique Identifier2016-2019 Count
A4
B4
C0
D4
E0

 

However, if I looked at only 2017-2018 in my slicer, then the results would be:

Unique Identifier2017-2018 Count
A2
B2
C2
D2
E0

Any assistance would this problem would be greatly appreciated.  Thanks in advance.

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ElenaN
Resolver V
Resolver V

Hello,

 

You can try this:

 

Measure =
VAR YearCount = COUNTROWS(FILTERS(Table3[Year]))
RETURN
IF(DISTINCTCOUNT(Table3[Year]) = YearCount, YearCount, 0)
 
Regards,
ElenaN

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.