Count of ID joining selected dataset

Anonymous — Tue, 18 Oct 2022 08:31:17 GMT

Hi,
I'm trying to get the below result:
In the dataset where the user can put filters on whatever dimensions/combination of dimensions I need to find the first appear of Employee and could how many those "first appear" are per month. For example:

My full Dataset:

Date	Name	Category	Job Type
1-1-2020	Harry	One	11
1-2-2020	Harry	One	11
1-3-2020	Harry	One	11
1-4-2020	Harry	Two	11
1-5-2020	Harry	Two	11
1-6-2020	Harry	Two	11
1-7-2020	Harry	Two	22
1-8-2020	Harry	Two	22
1-9-2020	Harry	Two	22
1-10-2020	Harry	Three	22
1-11-2020	Harry	Three	22
1-12-2020	Harry	Three	22
1-1-2020	Sally	One	22
1-2-2020	Sally	One	22
1-3-2020	Sally	One	22
1-4-2020	Sally	One	22
1-5-2020	Sally	One	22
1-6-2020	Sally	One	22
1-7-2020	Sally	One	22
1-8-2020	Sally	Three	22
1-9-2020	Sally	Three	22
1-10-2020	Sally	Three	22
1-11-2020	Sally	Three	11
1-12-2020	Sally	Three	11

Here I sould get 2 for 1-1-2020 and 0 for the rest.

But when User will put filter on Category "One and "three" and Job Type "22":

Date	Name	Category	Job Type
1-10-2020	Harry	Three	22
1-11-2020	Harry	Three	22
1-12-2020	Harry	Three	22
1-1-2020	Sally	One	22
1-2-2020	Sally	One	22
1-3-2020	Sally	One	22
1-4-2020	Sally	One	22
1-5-2020	Sally	One	22
1-6-2020	Sally	One	22
1-7-2020	Sally	One	22
1-8-2020	Sally	Three	22
1-9-2020	Sally	Three	22
1-10-2020	Sally	Three	22

I should get 1 for 1-10-2020 (Harry) ,1 for 1-1-2020 (Sally) and 0 for the rest.

The easiest way to do this would be to use lookupvalue on date +Name, but lookupvalue is a custom column function so is not dynamic/reacting to filter change.
My second idea was to create a key and key - 1 month which would be a concatenation of the dimensions where the user filtered SOMETHING, and it worked... almost. it was also caching change in dimension, not only "joining it" (first appearance)

Does anyone have and idea how to resolve above problem?

Re: Count of ID joining selected dataset

Jihwan_Kim — Tue, 18 Oct 2022 09:18:52 GMT

Hi,

Please check the below picture and the attached pbix file.

First appear measure: = VAR _firstappeardatebyname = ADDCOLUMNS ( DISTINCT ( 'Name'[Name] ), "@firstappeardate", CALCULATE ( MIN ( Data[Date] ), FILTER ( ALLSELECTED ( Data ), Data[Name] = EARLIER ( 'Name'[Name] ) ) ) ) RETURN COUNTROWS ( FILTER ( _firstappeardatebyname, [@firstappeardate] = MAX ( 'Calendar'[Date] ) ) ) + 0

topic Count of ID joining selected dataset in DAX Commands and Tips

Count of ID joining selected dataset

Re: Count of ID joining selected dataset