Forum Discussion
Measure for a table
Hi all,
I need some help creating a table in a report.
I have a table named ‘NP’ with several columns, one of them is ‘Register’ and another one named ‘ID’, that represents a company. In NP table some companies have several registries. This table only shows the latest record for which a company has had an NP active.
Then I have another table ‘PPL’ that only has three columns, it shows for each register the different time ranges it has been active, it has ‘StartDate’ and ‘EndDate’, with several records for each register.
I’ve created a calendar table ‘Fechas’ and a measure ‘Amount’ to count the Registers.
Amount = VAR selecteddate =
DATE ( SELECTEDVALUE ( Fechas[Año], YEAR ( MAX ( Fechas[Date] ) ) ),
SELECTEDVALUE ( Fechas[MesNo], MONTH ( MAX ( Fechas[Date] ) ) ),
SELECTEDVALUE ( Fechas[Date], DAY ( MAX ( Fechas[Date] ) ) )
)
RETURN
CALCULATE (DISTINCTCOUNT(PPL[Register])
,
FILTER (
PPL,
PPL[StartDate] <= selecteddate && PPL[EndDate] >= selecteddate
)
)
This measure helped me in counting the active registers at any selected date and corssfilter it with city, etc.
Now I’d like to add a table to the report showing how many Companies (via IDs) own how many registers, something like this:
NPs | Number of Companies |
1 | 3255 |
2 | 1233 |
3 | 598 |
4 | 456 |
5 | 111 |
6 | 59 |
7 | 40 |
8 | 33 |
9 | 29 |
10 | 12 |
>10 | 169 |
Total | 5995 |
So 3225 companies (IDs) had 1 register and only 169 companies had more than ten registers.
Do you know how I can achieve this? I think I need a new measure, that counts the number of registers and groups them by ID, I've tried doing that but cannot get to the desired result.
Many thanks.
3 Replies
Hi veri ,
Can you provide some test data about the data model so that I can answer your question as soon as possible.
Best Regards,
Adamk KongHi Anonymous ,
Yes, of course. Please see below:
The relationship:
PPL NP
Register *:1 Register
NP table sample data:
Register
Serial
EGP
PPLEndDate
AP
CompanyID
7
671
1979
30/06/2023
16,5
172349573016
12
8612
1979
31/10/2024
16,5
47685043327
24
6518
1982
02/08/2021
16,5
243672296763
37
912
1980
30/09/2024
17,1
379565974126
39
9141
1980
31/08/2022
16,5
964123160093
42
3711
1979
13/03/2024
24
243672296763
4288
9741
1984
01/08/2022
16,5
243672296763
14065
7610
1993
01/08/2022
16,5
243672296763
14397
9315
1965
30/09/2024
16,5
379565974126
95650
6472
1991
30/09/2024
16,5
379565974126
111578
4972
1979
31/07/2024
25,5
243672296763
112325
9474
1979
02/09/2024
25,5
964123160093
129471
6578
1995
01/07/2024
24
80084620022
130546
3412
1995
30/09/2024
18
349059847
130745
2313
1995
30/09/2024
18
349059847
139348
7147
1998
02/09/2024
18
964123160093
141008
4812
1997
30/09/2024
18
349059847
141765
9711
2001
02/09/2024
25,5
964123160093
161397
7893
2008
01/07/2024
21
80084620022
163973
4821
2011
01/07/2024
24
80084620022
168471
2674
2012
01/07/2024
24
80084620022
170647
1571
2013
30/09/2024
24,9
349059847
172496
5942
2013
01/07/2024
24
80084620022
176429
5596
2015
01/07/2024
21
80084620022
185671
6924
2017
30/09/2024
16,5
349059847
187846
8674
2017
30/09/2024
24
349059847
198347
5046
2022
16/03/2024
25,5
349059847
200604
1786
2023
10/03/2024
24,9
349059847
200876
7315
2023
16/03/2024
24,9
349059847
202873
5631
2023
10/03/2024
24
349059847
206712
8924
2023
16/03/2024
24
349059847
PPL sample data will be in another comment since it's too long and I can't post it on the same message.
With the sample data, and assuming the user selects 31/12/2023 as the date, the table I'd like to add to the report would look like this:
Registry
Companies
1
1
2
1
3
2
4
0
5
0
6
1
7
0
8
0
9
0
10
0
>10
1
Total
6
Thank you and best regards,
Veri.
PPL table sample data:
Registry
FY
StartDate
EndDate
7
2022
28/06/2022
30/06/2023
7
2021
09/07/2021
30/06/2022
12
2023
10/11/2023
31/10/2024
12
2022
08/11/2022
31/10/2023
24
2020
28/08/2020
02/08/2021
24
2019
21/01/2020
31/07/2020
24
2018
15/11/2018
31/07/2019
33
2013
02/10/2013
30/09/2014
33
2012
24/09/2012
30/09/2013
33
2010
04/10/2010
30/09/2011
39
2021
16/08/2021
31/08/2022
39
2020
05/08/2020
31/08/2021
112325
2023
04/08/2023
02/09/2024
112325
2022
01/08/2022
31/08/2023
112325
2021
16/08/2021
31/08/2022
112325
2020
05/08/2020
31/08/2021
112325
2019
13/08/2019
30/08/2020
112325
2018
10/08/2018
02/09/2019
112325
2017
22/08/2017
31/08/2018
4288
2021
01/11/2021
01/08/2022
4288
2020
28/04/2021
02/08/2021
4288
2019
13/11/2019
31/07/2020
4288
2018
15/11/2018
31/07/2019
139348
2023
04/08/2023
02/09/2024
139348
2022
01/08/2022
31/08/2023
141765
2023
04/08/2023
02/09/2024
141765
2022
01/08/2022
31/08/2023
141765
2021
16/08/2021
31/08/2022
14065
2021
01/11/2021
01/08/2022
14065
2020
28/08/2020
02/08/2021
14065
2019
25/11/2019
31/07/2020
14065
2013
18/06/2013
31/12/2013
111578
2023
16/11/2023
31/07/2024
111578
2021
01/11/2021
01/08/2022
111578
2021
20/08/2021
31/08/2022
37
2023
27/06/2023
30/09/2024
37
2022
30/09/2022
02/10/2023
37
2021
27/09/2021
30/09/2022
42
2023
09/08/2023
13/03/2024
42
2022
19/01/2023
13/08/2023
95650
2023
26/06/2023
30/09/2024
95650
2022
07/07/2022
31/08/2023
95650
2020
31/08/2020
31/08/2021
14397
2023
27/06/2023
30/09/2024
14397
2023
07/06/2023
31/07/2024
14397
2023
03/05/2023
30/04/2024
130546
2023
27/09/2023
30/09/2024
130546
2022
29/09/2022
02/10/2023
130546
2021
30/09/2021
30/09/2022
130745
2023
27/09/2023
30/09/2024
130745
2022
29/09/2022
02/10/2023
130745
2021
30/09/2021
30/09/2022
141008
2023
27/09/2023
30/09/2024
141008
2022
29/09/2022
02/10/2023
170647
2023
27/09/2023
30/09/2024
170647
2022
29/09/2022
02/10/2023
170647
2021
30/09/2021
30/09/2022
185671
2023
27/09/2023
30/09/2024
185671
2022
29/09/2022
02/10/2023
185671
2021
30/09/2021
30/09/2022
187846
2023
27/09/2023
30/09/2024
187846
2022
29/09/2022
02/10/2023
198347
2023
16/06/2023
01/07/2024
198347
2022
22/06/2022
30/06/2023
200876
2023
16/06/2023
01/07/2024
200876
2022
22/06/2022
30/06/2023
206712
2023
16/06/2023
01/07/2024
206712
2022
22/06/2022
30/06/2023
202873
2023
16/06/2023
01/07/2024
200604
2023
16/06/2023
01/07/2024
200604
2022
22/06/2022
30/06/2023
200604
2021
11/03/2022
30/06/2022
129471
2023
15/06/2023
01/07/2024
129471
2022
01/06/2022
30/06/2023
129471
2021
10/11/2021
30/06/2022
161397
2023
15/06/2023
01/07/2024
161397
2022
01/06/2022
30/06/2023
161397
2021
10/11/2021
30/06/2022
163973
2023
15/06/2023
01/07/2024
163973
2022
01/06/2022
30/06/2023
163973
2021
10/11/2021
30/06/2022
168471
2023
15/06/2023
01/07/2024
168471
2022
01/06/2022
30/06/2023
172496
2023
15/06/2023
01/07/2024
172496
2022
01/06/2022
30/06/2023
172496
2021
10/11/2021
30/06/2022
176429
2023
15/06/2023
01/07/2024
176429
2022
01/06/2022
30/06/2023
176429
2021
10/11/2021
30/06/2022
176429
2020
17/11/2020
30/06/2021
