many to many relationship

[abc_777]

Hi,

I have a many to many relation in my power bi desktop data model. 

i have a summary table that has column invoice_no matches to sales table invoice_no column with active relationship and has many to many relation. This table i can't but has to be in this data model otherwise my result comes wrong.

the problem is,

I have customer table that has column store_Id column matches to sales table store_ID column with an in-active relationship and has one to many relation

N.B. I created third table in between customer and sales table but when i join sales to third table it still take many to many relation

So i use userelationship to active this in-active relation between customer and sale

CALCULATE(

'Measure Table'[Total Quantity in Piece (Measure)],

USERELATIONSHIP(

'bm_retail_t sale'[STORE_CODE],

'bm_retail_t customer'[STORE_CODE])

I have employee table that has one to one relation with customer table using employee_code. but if I join employee table to sales table it takes many to many relation So i keep relation with customer table

Now The measure I try to create 

% of Total Sale (CONCATANATEX Assignment) =

VAR AllExceptQuantity=

                  CALCULATE(

                                 [Total Sale (Taka) (Measure)],

                    ALLEXCEPT(

                                  'bm_retail_t sale',

                                  BMCalendar,

                                  'bm_retail_t employee'[EMPCODE_NAME_Conca])

                       )

VAR Ratio=

               DIVIDE(

                    [Total Sale (Taka) (Measure)],

                    AllExceptQuantity,

                 BLANK()

               )

       RETURN

            Ratio

the problem is AllExcept DAX function not use many to many relationship and removefilter dax function only tale one column. 

so how to i can use instead of using allexcept function in many to many relationship

[amitchandak]

@abc_777 , Try like

% of Total Sale (CONCATANATEX Assignment) =
VAR AllExceptQuantity=

CALCULATE(
[Total Sale (Taka) (Measure)],
filter(ALL('bm_retail_t employee')
'bm_retail_t employee'[EMPCODE_NAME_Conca] = max('bm_retail_t employee'[EMPCODE_NAME_Conca])
))
VAR Ratio=
DIVIDE(
[Total Sale (Taka) (Measure)],
AllExceptQuantity,
BLANK()
)
RETURN
Ratio

[abc_777 · Accepted solution]

Hi @amitchandak ,

Your measure without USERELATIONSHIP formula I got following result

% of Total Quantity (CONCATANATEX Assignment) =

VAR AllExceptQuantity=

CALCULATE(

'Measure Table'[Total Quantity in Piece (Measure)],

filter(ALL('bm_retail_t employee'),

'bm_retail_t employee'[EMPCODE_NAME_Conca] = max('bm_retail_t employee'[EMPCODE_NAME_Conca])

))

VAR Ratio=

DIVIDE(

'Measure Table'[Total Quantity in Piece (Measure)],

AllExceptQuantity,

BLANK()

)

RETURN

Ratio

------------------------------------------------

Your measure with  USERELATIONSHIP I got following result

 

% of Total Quantity (CONCATANATEX Assignment) =

VAR AllExceptQuantity=

CALCULATE(

'Measure Table'[Total Quantity in Piece (Measure)],

USERELATIONSHIP(

'bm_retail_t sale'[STORE_CODE],

'bm_retail_t customer'[STORE_CODE]),

filter(ALL('bm_retail_t employee'),

'bm_retail_t employee'[EMPCODE_NAME_Conca] = max('bm_retail_t employee'[EMPCODE_NAME_Conca])

))

VAR Ratio=

DIVIDE(

'Measure Table'[Total Quantity in Piece (Measure)],

AllExceptQuantity,

BLANK()

)

RETURN

Ratio

--------------------------

my expectation would be as follows (I should give this before)

here is you see is, (2.61/2088.32) * 100 = 0.12% and same as other rows

[abc_777]

I am trying to acheive Marketing personwise sales percentage. anyone has any idea with data model that i have given. I know the dababase design is very poor  but data base is not in my hand i can't do much. so please help me out to find the solution form this data model

thx