Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JuanDtM23
Helper II
Helper II

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: 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

JuanDtM23_0-1600743697125.png

Below you can see that I have two measures:

Sin título.png

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:

Sin título.png

I hope you can help me.

Thanks a lot

2 ACCEPTED SOLUTIONS
PaulDBrown
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

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

View solution in original post

5 REPLIES 5
PaulDBrown
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hello, Thank you very much.

I will try

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

v-deddai1-msft
Community Support
Community Support

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors