Create Supplemental Value Table

[JuanDtM23]

Hello experts,

I hope you can help me.

I put them in context.

I have three databases:

• Servicios_habilitados: Indicates skillful health services for each city
• Contracted services: Indicates the services contracted by the company in each city
• Tracer Services: This is the list of services that the company must provide.

Two new tables are created from those databases:

• Contracted tracer services: List of tracer services that the company has contracted in each city.
• Plotter services enabled: List of tracer services that are enabled in each city.

The relationships are as follows:

• Servicios_Trazadores_Habilitados *:1 Servicios_Trazadores
• Servicios_Trazadores 1:* Contracted Services
• Contracted Services *:* Servicios_Habilitados
• Servicios_Habilitados *:1 Servicios_Trazadores_Habilitados

Below you can see that I have two measures:

Contracted tracer services: Counts the number of tracer services contracted in each city

Plotter Services Enabled: Counts the number of plotter services enabled in each city

In the table on the left you have the number of plotting services contracted and on the right the plotter services enabled.

My intention is to:

Show in the table on the right only the tracer services that are not contracted in each of the municipalities.

As illustrated below:

I hope you can help me.

Thanks a lot

[PaulDBrown · Accepted solution]

@JuanDtM23

I suggest you eliminate the many-to-many relationship between your Servicios_habilitados (Nombre_municipio) and CONTRACTED SERVICES (MUNICIPIO).

Instead, create a Dimension table for unique "municipalities" values and create one-to-many relationships between this Dimension table and Servicios_habilitados (Nombre_municipio) and CONTRACTED SERVICES (MUNICIPIO).

To identify services that have not been contracted to us:
Services not contracted ?
VAR CONtServices - VALUES(SERVICIOS_CONTRATADOS [COD_SERVICIO]
VAR ServObl - VALUES(SERVICIOS_TRAZADORES[COD SERVICE]
Return

COUNTROWS(EXCEPT(ServObl, ContServices)

[PaulDBrown · Accepted solution]

@JuanDtM23

I suggest you eliminate the many-to-many relationship between your Servicios_habilitados (Nombre_municipio) and CONTRACTED SERVICES (MUNICIPIO).

Instead, create a Dimension table for unique "municipalities" values and create one-to-many relationships between this Dimension table and Servicios_habilitados (Nombre_municipio) and CONTRACTED SERVICES (MUNICIPIO).

To identify services that have not been contracted to us:
Services not contracted ?
VAR CONtServices - VALUES(SERVICIOS_CONTRATADOS [COD_SERVICIO]
VAR ServObl - VALUES(SERVICIOS_TRAZADORES[COD SERVICE]
Return

COUNTROWS(EXCEPT(ServObl, ContServices)

[JuanDtM23]

Hello, Thank you very much.

I will try

[v-deddai1-msft · Accepted solution]

Hello @JuanDtM23 ,

Depending on my understanding of your description, you can create a measure for the visual level filter:

Measure = var a =CALCULATETABLE(EXCEPT(VALUES(Servicios_habilitados[serv_codigo]),VALUES('SERVICIOS CONTRATADOS'[COD_SERVICIO]))) return IF(MAX(Servicios_Trazadores_Habilitados[COD])in a,1,0)

Add it to Services Plotters Enabled table visual level filter and set it to 1:

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

Best regards

Dedmon Dai

[v-deddai1-msft]

Hi @JuanDtM23 ,

Is there any column to distinguish between municipalities and ordinary cities? Or you can create a measure for it and add it to visual level filter on the right table.

It is difficult to understand your requirements only through your description, perhaps you can provide some sample data.

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

Best Regards,

Dedmon Dai

[JuanDtM23]

Hello @v-deddai1-msft ,

No, the Contracted Services table has a column called "Municipality" which contains both ordinary cities and municipalities, for its part the table "Servicios_Habilitados" has a column called "Nombre_Municipio_" which also contains ordinary cities and municipalities.

I attach the . Pbix for greater understanding:

https://mutualsereps-my.sharepoint.com/:u:/g/personal/jtorres_mutualser_org/Eetpi9jlVItLv8aTtxRywysB...