Forum Discussion

abc_777's avatar
abc_777
Solution Specialist
4 years ago
Solved

Table over a table

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

Need Help

8 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

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

    • abc_777's avatar
      abc_777
      Solution Specialist
      4 years ago

      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's avatar
        PaulDBrown
        Community Champion
        4 years ago

        Can't you create a bridge table?

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    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's avatar
      abc_777
      Solution Specialist
      4 years ago

      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