Measure for a table

[veri]

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.

[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

[veri]

Hi @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. 

[Anonymous]

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 Kong