DAX - Calculate the quantity of cross-sales in a physical table.

[brunovetupcombr]

The goal of the formula I am trying to create is to generate a table with two columns: one containing the product names from the dProdutosAux table, and another containing the values of cross-selling for those products with the selected "CONSULTA" product from the fVendas table.

To achieve this objective, the formula I am attempting involves:

1. Selecting the product name from the dProdutosAux table in the current context using SELECTEDVALUE(dProdutosAux[nomeProduto]).

2. Intersecting two calculated tables:
- The first calculated table contains the sales codes where the product is "CONSULTA," filtered to exclude blank rows (NOT(ISBLANK(fVendas[codigoCliente]))).
- The second calculated table contains the sales codes where the product is the selected product from the dProdutosAux table, filtered to exclude blank rows and taking into account the relationship between the tables.

3. Counting the number of rows in the intersection table to obtain the quantity of cross-selling.

4. Creating a calculated table using the ADDCOLUMNS function, which adds a "qtdCruzada" column to the dProdutosAux table and associates the calculated value of cross-selling with the correct product.

The measure should return a table with the products from the dProdutosAux table and the quantity of cross-selling between each product and the "CONSULTA" product. However, it is not correctly recognizing the value of the selected product.

'''

produtoY =

VAR vNomeProdutoSelecionado = "CONSULTA"

VAR vNomeProdutoAux = SELECTEDVALUE(dProdutosAux[nomeProduto])

VAR vIntersecao =  

    INTERSECT(

        CALCULATETABLE(      

            VALUES(fVendas[codigoVenda]),  

            NOT(ISBLANK(fVendas[codigoCliente])),

            FILTER(fVendas, fVendas[nomeProduto] = vNomeProdutoSelecionado)

        ),

        CALCULATETABLE(  

            VALUES(fVendas[codigoVenda]),

            NOT(ISBLANK(fVendas[codigoCliente])),

            FILTER(fVendas, fVendas[nomeProduto] = vNomeProdutoAux),  

            USERELATIONSHIP(dProdutosAux[nomeProduto], fVendas[nomeProduto])

        )

    )

VAR vResultado =  

    COUNTROWS(vIntersecao)  

VAR vtabela0 =

    ADDCOLUMNS(

        CALCULATETABLE(

            VALUES(dProdutosAux[nomeProduto]),

            USERELATIONSHIP(fVendas[nomeProduto], dProdutosAux[nomeProduto])

        ),

        "qtdCruzada", vResultado

    )

RETURN  

    vtabela0

'''

Result:

Expected Result:

Sample Data:
dProduto:

nomeProduto

EXAME HEMOGRAMA

DIÁRIA

MANUTENÇÃO

ULTRASSOM

CONSULTA

EXAME CHEM

RAIO X

APLICAÇÃO

VACINA

dProdutoAux:

nomeProduto

EXAME HEMOGRAMA

DIÁRIA

MANUTENÇÃO

ULTRASSOM

CONSULTA

EXAME CHEM

RAIO X

APLICAÇÃO

VACINA

fVendas:

codigoVenda	codigoCliente	nomeProduto
00000001	000001	CONSULTA
00000002	000002	EXAME HEMOGRAMA
00000002	000002	DIÁRIA
00000003	000003	MANUTENÇÃO
00000003	000003	ULTRASSOM
00000003	000003	CONSULTA
00000004	000005	EXAME CHEM
00000004	000005	RAIO X
00000004	000005	APLICAÇÃO
00000005	000001	CONSULTA
00000005	000001	DIÁRIA
00000006	000006	VACINA
00000006	000006	ULTRASSOM
00000006	000006	CONSULTA
00000007	000008	DIÁRIA
00000007	000008	CONSULTA
00000007	000008	VACINA
00000007	000008	EXAME HEMOGRAMA
00000008	000009	EXAME HEMOGRAMA
00000008	000009	CONSULTA
00000008	000009	RAIO X
00000009	000010	MANUTENÇÃO
00000009	000010	EXAME HEMOGRAMA
00000009	000010	CONSULTA

Relationships:

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...