Dax filtering using other table column that's not related because of the star schema limitation

[supreethm19]

Using the below data model, I want to create a measure RepRevenue based on the "RepProdMap" table & "Product" table.

- I will pull in the SalesRep & RepRevenue into a pivot table

- I need the RepRevenue to sum up for only the products that a SalesRep is assosciated in the "RepProdMap" Table

- Basically I need to figure out a way to pass the ProductId Column in the "RepProdMap" Table to "Product" Table

- If I create a join between Porduct & RepProdMap the data model will form the "Loops"

- If I create a inactive relationship and the use "USERELATIONSHIP" the data isn't filtering by salesrep, the data filters through Product only

[Anonymous · Accepted solution]

You can create a calculated column in Product table to take RepID to the Product table, with LOOKUPVALUE:

RepID = LOOKUPVALUE(RepProdMap[RepID], RepProdMap[ProdID], Product[ProdID])

And now you sum only the Revenue where you have a RepID with this measure:

Revenue Measure = CALCULATE(SUM(Orders[Revenue]), Product[RepID] <> BLANK())

[v-cherch-msft]

Hi @supreethm19

You may try to check the cross filter direction in the relationship or add filter in your measure. Here is the document for you.

https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

Regards,

Cherie