cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yforti
Helper II
Helper II

Help for creating measures from related tables

Hi guys!

I'm trying to create a measure that relates my fact table to other dimension tables.

 

The photo below shows the schematic of my data model.


dProduto = dProduct
dPacote = dPackage
De/Para = package rule validation table

yforti_1-1635264162641.png

I need to create a measure that relates the dProduct, dPackage and fVision tables.

 

The dProduct table has all the products of the model, with their code and type:

 

Headers: 
CD_Produto = code of product
DS_Produto = name of product
Tipo_Produto = type of product

yforti_2-1635264337179.png

 

The dPackage table contains all the packages of the model, with their code, type and value.

A package covers multiple products:

Headers:
Ds_produto = name of package
NR_SER_PROC_PACOTE = code of package
VL_TOTAL = value of package
Tipo Pacote = type of package

yforti_3-1635264472631.png

 

We still have a third table that makes the relationship with a dPackage, this table shows that each package must have at least one product of a specific type:

Headers:
Nome do Pacote:  name of package
Material Osteossintese: type of product the package must have
Material Especial: type of product the package must have

yforti_4-1635264809663.png

 

Based on this table, I need to create a measure that validates this rule, that is, that validates if each package in the From/To table has a certain product of the specified type.

 

For example, let's take the first packet from the from/to table:

yforti_7-1635265310245.png

In this example, package A must have at least one product of type 1 and one product of type 2. (Remembering that it is the dProduct table that contains the products and types.)

As the from/to table relates to the dPackage table, and dPackage relates to fVision, we can see in the fact table which packages need to be validated.

yforti_8-1635265801259.png

Looking at the same package as the example in the dPackage table:

yforti_9-1635265898418.png

We can see that the example package has a specific Number, this number is the key that makes the relationship between the table dPackage and fVision.
In this case the number is: 533762565

 

Looking at the same package as the example in the fVision table:

yforti_10-1635266049081.png

We can see that by the package number (533762565), there are several products that are inside that package.

 

What I need is to create a measure that validates whether within these products there is at least one type 1 product and another type 2 product. Because, remember, this is a rule that comes from the from/to table:

yforti_7-1635265310245.png

And remember that the table containing the types of products is dProduct, which is related to fVision.

yforti_11-1635266318955.png

 

https://we.tl/t-bAbtDdfbJW  link with pbi data model.

1 REPLY 1
V-pazhen-msft
Community Support
Community Support

@yforti 

Due to security reason, please do not post your working pbix to the community. And you are unlikely get responses with such huge amount of information and the entire model throw to community users. You can delete this post, and repost with a sample pbix tables without sensitive data and include only the required tables and dax. 

 

 

Best Regards

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.