Forum Discussion

JuanDtM23's avatar
JuanDtM23
Helper II
5 years ago
Solved

Create Supplemental Value Table

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: Indi...
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    @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)

  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    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:

    Capture.PNG

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

    Best regards

    Dedmon Dai

PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

@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's avatar
    JuanDtM23
    Helper II
    5 years ago

    Hello, Thank you very much.

    I will try

    • v-deddai1-msft's avatar
      v-deddai1-msft
      Community Support
      5 years ago

      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:

      Capture.PNG

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

      Best regards

      Dedmon Dai