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
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
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
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:
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.
Looking at the same package as the example in the dPackage table:
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:
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:
And remember that the table containing the types of products is dProduct, which is related to fVision.
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.