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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.