Table over a table

[abc_777]

Hi,

I have a following relationship where the customer table is related to ssummary (one to many) tables then summary table have a relation to the sale table (many to many)

and so when i do the following measure it gives me the incorrect result

can someone help me out, please

% of Actual Quantity (CONCATANATEX Assignment) =

VAR AllExceptQuantity=

CALCULATE(

'Measure Table'[Actual Quantity (Measure) (KG)], >>>>>>>> this a measure of total quantity

ALLEXCEPT(

'bm_retail_t ssummary', >>> instead of this one here 

'bm_retail_t sale', >>>>>>>> i want  to be here

'bm_retail_t customer'[EMPLOYEE_CODE])

)

VAR Ratio=

DIVIDE(

'Measure Table'[Actual Quantity (Measure) (KG)],

AllExceptQuantity,

BLANK()

)

RETURN

Ratio

thanks

[PaulDBrown]

If Customer ID and Customer Code are different fields you can't create a relationship (and you should delete the one you have in place).

As regards your other question, you can use CROSSFILTER to activate a many-to-one relationship 

[abc_777]

thanks paul,

i have made the solution by merging columns in another way. but really great that you always touch on me and suggest me. thanks you again

[PaulDBrown]

The wrong result may have to do with the fact that two tables have a many-to-many relationship

[abc_777]

yes, paul, and the database designer make this incorrect table relationship. there is no Key that I can join with the customer table direct to the sale table.

So I need a measure or DAX formula or solution that I can over calculate customer and sale table

thanks

[PaulDBrown · Accepted solution]

Can't you create a bridge table?

[abc_777]

i have company code in the sale table and ssummary table and 

customer_id  in the customer table and Customer_code in ssummary table

would be able to help me how i create bridge using distinct

[PaulDBrown]

You can create the bridge table using the equivalent to:

Customer Table =
DISTINCT (
    UNION (
        VALUES ( 'Customer Table'[Customer ID] ),
        VALUES ( 'Summary table'[Customer ID] )
    )
)

[abc_777]

Hi paul

thanks for your reply.

I have many-to-many relation to ssummary and sales table that has 

customer_id  in the customer table and Customer_code in ssummary table

how i make bridge table in this case 

moreover, i am querying from customer tale to sales but ssummary table is in middle of customer and sales table