Newest values for sums of discrete ids

[StefanLux]

Hi all,

I have a quite big Data-Table (PowerPivot), here are just some example rows/colomns:

pers_id	date	measure	age
12	2024-01-31	1	42
13	2024-01-31	0	42
18	2024-01-31	1	43
19	2024-01-31	1	17
20	2024-01-31	0	42
12	2024-02-29	0	43
21	2024-02-29	0	43
18	2024-02-29	0	43
12	2024-03-31	1	43
20	2024-03-31	0	43

The table shows some reports (from date) for person (pers_id) with result measure and age.

I need the results for single month, but also for time periods.

My pivot tables look like:

Age	discrete Pers_id
17	1
42	3
43	4
Total	6

and

Measure	discrete Pers_id
0	5
1	3
Total	6

which is of course wrong, because the pers_id are not unique in the table.

But I just need the newest data depending on the date (per pers_id).

So the january data for pers_id 12 is only needed, if I choose january in the date slicer.
The will never be duplicates with same pers_id and date.

Can you help me?

Thanks in advance,

Stefan

[v-rongtiep-msft]

Hi @StefanLux ,

Please have a try,.

Create a meaure.

Measure2 =
VAR _1date =
    SELECTEDVALUE ( 'Table 2'[date] )
VAR _2 =
    CALCULATE (
        COUNT ( 'Table'[age] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[date] = SELECTEDVALUE ( 'Table'[date] )
                && 'Table'[age] = SELECTEDVALUE ( 'Table'[age] )
        )
    )
RETURN
    IF (
        MAX ( 'Table'[date] ) = _1date,
        _2,
        IF ( _1date = BLANK (), _2, BLANK () )
    )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StefanLux]

Hi Rongtie,

thanks for your quick response.

I checked your measure, but it not yet the solution.

What I have to do is to count the discrete pers_id per age/month and age/period.

So, for this example the desired output would be:

January
Age	discrete Pers_id
17	1
42	3
43	1
Total	5
February
Age	discrete Pers_id
43	3
Total	3
January + February
Age	discrete Pers_id
17	1
42	2
43	3
Total	6
February + March
Age	discrete Pers_id
43	4
Total	4
January - March
Age	discrete Pers_id
17	1
42	1
43	4
Total	6

You think it is possible?

Thanks in advance,

Stefan

[v-rongtiep-msft]

Hi @StefanLux ,

Maybe I really get what you're saying.

Please refer to my pbix file.

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StefanLux]

Hi Rongtie,

thanks again for your quick response.

Please check, there are only 6 people (show only the newest row per pers_id), but you have 10.

Thanks in advance,

Stefan

[v-rongtiep-msft · Accepted solution]

Hi @StefanLux ,

Please refer to .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.