Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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.
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
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.
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
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.
User | Count |
---|---|
89 | |
73 | |
68 | |
64 | |
56 |
User | Count |
---|---|
103 | |
94 | |
74 | |
60 | |
59 |