topic Re: Counting Value Based On It's Last Appearence in DAX Commands and Tips

Counting Value Based On It's Last Appearence

Atanas_Atanasov — Thu, 08 Aug 2024 10:47:53 GMT

Dear All,

I have a report which contains person ID.

I'd like to calculate the distinct count of that person ID, based on their last appearence.

For example I have 1 person who appears only on 01/01/2024 and another person who appears on all other days, but their last appearence is 01/26/2024 (screenshot 1).

I'd like to show a table which has date and the distinct count next to it and it should look like that:

Date: Count Of Person ID

01/01/2024 1

01/26/2024 1

NOTE: ID2 appears in multiple days, but I am looking for is to show only their last date of appearence in the report.

I will be deeply grateful if you can help me with that.

NOTE: This is how my datamodel looks (screenshot 2)

All due respect,

Atanas

Re: Counting Value Based On It's Last Appearence

bhanu_gautam — Thu, 08 Aug 2024 11:09:09 GMT

Atanas_Atanasov , First create a calculated column for last appearance

LastAppearanceDate =

CALCULATE(

MAX(PersonAppearances[AppearanceDate]),

ALLEXCEPT(PersonAppearances, PersonAppearances[PersonID])

)

Then create a summary table to show distinct value

SummaryTable =

SUMMARIZE(

PersonAppearances,

PersonAppearances[LastAppearanceDate],

"CountOfPersonID", DISTINCTCOUNT(PersonAppearances[PersonID])

)

Re: Counting Value Based On It's Last Appearence

Atanas_Atanasov — Thu, 08 Aug 2024 11:54:07 GMT

bhanu_gautam - Thank you for the suggestion, but is it possible to do it with a measure?

Additinionaly, when I add more months of data the date in the column you suggest automatically changes to the latest in the dataset. While I need this to be calculated for the month in the filter.

Thank you,

Atanas

Re: Counting Value Based On It's Last Appearence

SachinNandanwar — Thu, 08 Aug 2024 11:53:34 GMT

Is it possible that there will be multiple entries of a person across the same dates ?

If not then just use this Measure

MaxDatePresence = CALCULATE(MAX(person[date].[Date]),INDEX(1,DISTINCT(ALL(person)),ORDERBY(person[date].[Date],DESC),PARTITIONBY(person[person])))

Source Data >>
date,personid
1/8/2024,1
2/8/2024,1
5/8/2024,1
8/8/2024,1
11/8/2024,1
3/8/2024,2
4/8/2024,2
10/8/2024,2

o/p >>

Re: Counting Value Based On It's Last Appearence

Atanas_Atanasov — Thu, 08 Aug 2024 12:02:31 GMT

SachinNandanwar - Can you please show it as a count of the people? Thank you very much for your help!

Re: Counting Value Based On It's Last Appearence

SachinNandanwar — Thu, 08 Aug 2024 12:09:21 GMT

Atanas_Atanasov
If there is one unique entry of a person across a given date the count would always be one for that given date.

Re: Counting Value Based On It's Last Appearence

Atanas_Atanasov — Thu, 08 Aug 2024 13:46:19 GMT

Thank you again, but the result this measure provides is the last date in my dataset (screenshot 1). Except for line 1.

I think this is not working due to the fact I have calendar table in my data model (screenshot 2) from my original post, but not sure how to resolve it,

Atanas

Re: Counting Value Based On It's Last Appearence

SachinNandanwar — Thu, 08 Aug 2024 13:48:52 GMT

Could you please share some sample data ?

Re: Counting Value Based On It's Last Appearence

Atanas_Atanasov — Thu, 08 Aug 2024 13:56:28 GMT

That's my dataset for January. The only think you have to do is create a calendar and you will have the same datamodel. The relationship is based on calednar.date -> table.MetricDate

MetricDate	App	Value	Refresh date	PersonId
1/1/2024	Word	918030	7/8/2024	032d88a7-7ea7-3195-829c-383e976f6cf3
1/2/2024	Word	933257	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/3/2024	Word	401560	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/4/2024	Word	37782	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/5/2024	Word	934145	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/6/2024	Word	625459	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/7/2024	Word	166553	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/8/2024	Word	210070	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/9/2024	Word	524790	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/10/2024	Word	218922	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/11/2024	Word	867779	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/12/2024	Word	281966	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/13/2024	Word	719946	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/14/2024	Word	518357	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/15/2024	Word	355841	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/16/2024	Word	155917	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/17/2024	Word	577269	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/18/2024	Word	154694	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/19/2024	Word	90236	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/20/2024	Word	541938	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/21/2024	Word	390435	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/22/2024	Word	536700	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/23/2024	Word	436265	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/24/2024	Word	297394	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/25/2024	Word	963107	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3
1/26/2024	Word	671828	7/8/2024	031d88a7-7ea7-3195-829c-383e976f6cf3

Re: Counting Value Based On It's Last Appearence

SachinNandanwar — Thu, 08 Aug 2024 15:54:09 GMT

Have uploaded the pbix file here : https://file.io/JfCJG8VzeyOl

Re: Counting Value Based On It's Last Appearence

lbendlin — Thu, 08 Aug 2024 21:49:29 GMT

That's all the same person ID?

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Counting Value Based On It's Last Appearence

ThxAlot — Thu, 08 Aug 2024 21:57:08 GMT

Easy enough with a calculated column

Re: Counting Value Based On It's Last Appearence

Atanas_Atanasov — Fri, 09 Aug 2024 10:54:20 GMT

Thank you for your suggestion, but when added additional months of data, the dates in the column change to the last date in the dataset. I found the solution and will share it below.

Re: Counting Value Based On It's Last Appearence

Atanas_Atanasov — Fri, 09 Aug 2024 11:06:08 GMT

Ok, I found the solution.

1. Create table by Person ID, Calendar Date and the distinct count of the person ID. Then removing the filter context from the calendar date and the sorting column in the calendar table:

2. Grouped the table from step 1 by person id, while interating the group to get the last date (MAXX). And then selecting only the column with the latest date:

3. Then if the calendar date = the date found in step 2 return me the distinct count of the person id where the calendar date = last calendar date:

The result is exactly what I was looking for:

Thank you all for your help and suggestions. Hope this might help to you as well!

Have a nice day and great weekend,

Atanas

Re: Counting Value Based On It's Last Appearence

Anonymous — Mon, 12 Aug 2024 02:12:55 GMT

Hi Atanas_Atanasov ,

Could you please mark your solution? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Counting Value Based On It's Last Appearence

Atanas_Atanasov — Mon, 12 Aug 2024 06:34:47 GMT

Please consider it done.

Thank you,

Atanas