USERELATIONSHIP complex case

Question

Hi,&nbsp;I have a following business problem that I need your help solving.I have two Dimension tables - Account Type DIM and Volume Type DIMand One FACT table - Volume FACT (This is just a part of my complex data model)The relationships from DIM to FACT are one to many and all are inactive relationships (I know I can keep one active relationship but I have delibrately kept it inactive). I have marked all the relationships columns in the below screen shot.&nbsp;&nbsp;I want to calculate Total volume which can be done by summing the following columns =VOLUME FACT[manual_opened_usd_volume] +&nbsp;VOLUME FACT[manual_closed_usd_volume] +&nbsp;VOLUME FACT[copytrading_opened_usd_volume] +&nbsp;VOLUME FACT[copytrading_closed_usd_volume]Now In the report view I have the following slicersI want the Total Volume to change when I interact with the slicers.Let me share the DAX Expression that I wrote.Total Volume_new = VAR __Manual_open_volume =CALCULATE(&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; SUM(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[manual_opened_usd_volume]&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[Manual Open Volume Index],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Volume Type DIM'[Index]&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ),&nbsp; &nbsp; USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[Provider Index],&nbsp; &nbsp; &nbsp; &nbsp; 'Account Type DIM'[id]&nbsp; &nbsp; ),&nbsp; &nbsp; USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[Follower Index],&nbsp; &nbsp; &nbsp; &nbsp; 'Account Type DIM'[id]&nbsp; &nbsp; ))VAR __Manual_close_volume =CALCULATE(&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; SUM(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[manual_closed_usd_volume]&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[Manual Close Volume Index],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Volume Type DIM'[Index]&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ),&nbsp; &nbsp; USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[Provider Index],&nbsp; &nbsp; &nbsp; &nbsp; 'Account Type DIM'[id]&nbsp; &nbsp; ),&nbsp; &nbsp; USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[Follower Index],&nbsp; &nbsp; &nbsp; &nbsp; 'Account Type DIM'[id]&nbsp; &nbsp; ))VAR __Copytrading_open_volume =CALCULATE(&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; SUM(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[copytrading_opened_usd_volume]&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[Copy Trading Open Volume Index],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Volume Type DIM'[Index]&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ),&nbsp; &nbsp; USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[Provider Index],&nbsp; &nbsp; &nbsp; &nbsp; 'Account Type DIM'[id]&nbsp; &nbsp; ),&nbsp; &nbsp; USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[Follower Index],&nbsp; &nbsp; &nbsp; &nbsp; 'Account Type DIM'[id]&nbsp; &nbsp; ))VAR __Copytrading_close_volume =CALCULATE(&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; SUM(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[copytrading_closed_usd_volume]&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[Copytrading Close Volume Index],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Volume Type DIM'[Index]&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ),&nbsp; &nbsp; USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[Provider Index],&nbsp; &nbsp; &nbsp; &nbsp; 'Account Type DIM'[id]&nbsp; &nbsp; ),&nbsp; &nbsp; USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[Follower Index],&nbsp; &nbsp; &nbsp; &nbsp; 'Account Type DIM'[id]&nbsp; &nbsp; ))RETURN__Manual_open_volume+__Manual_close_volume+__Copytrading_open_volume+__Copytrading_close_volumeThe Result is not filtering the data as it should and giving the following error when i select provider / follower from account_type slicer&nbsp;&nbsp;&nbsp;

tamerj1 · Answer

Hi&nbsp;BI_kartik&nbsp;Please try
Total Volume_new =
SUMX (
    VALUES ( 'Account Type DIM'[id] ),
    VAR CurrentAccount = 'Account Type DIM'[id]
    RETURN
        CALCULATE (
            VAR __Manual_open_volume =
                CALCULATE (
                    SUM ( 'VOLUME FACT'[manual_opened_usd_volume] ),
                    USERELATIONSHIP ( 'VOLUME FACT'[Manual Open Volume Index], 'Volume Type DIM'[Index] )
                )
            VAR __Manual_close_volume =
                CALCULATE (
                    SUM ( 'VOLUME FACT'[manual_closed_usd_volume] ),
                    USERELATIONSHIP ( 'VOLUME FACT'[Manual Close Volume Index], 'Volume Type DIM'[Index] )
                )
            VAR __Copytrading_open_volume =
                CALCULATE (
                    SUM ( 'VOLUME FACT'[copytrading_opened_usd_volume] ),
                    USERELATIONSHIP ( 'VOLUME FACT'[Copy Trading Open Volume Index], 'Volume Type DIM'[Index] )
                )
            VAR __Copytrading_close_volume =
                CALCULATE (
                    SUM ( 'VOLUME FACT'[copytrading_closed_usd_volume] ),
                    USERELATIONSHIP ( 'VOLUME FACT'[Copytrading Close Volume Index], 'Volume Type DIM'[Index] )
                )
            RETURN
                __Manual_open_volume + __Manual_close_volume + __Copytrading_open_volume + __Copytrading_close_volume,
            'VOLUME FACT'[Provider Index] = CurrentAccount,
            'VOLUME FACT'[Follower Index] = CurrentAccount
        )
)

bi_kartik · Answer

Thank you for your response&nbsp;tamerj1.&nbsp;But your solution is not working. Im getting blank if I select between provider and follower. Also, with no filter applied, the total volume is not correct.

tamerj1 · Answer

BI_kartik&nbsp;This is the logic that I understood from your DAX code. I believe this is the condition that generates blanks everywhere
            'VOLUME FACT'[Provider Index] = CurrentAccount,
            'VOLUME FACT'[Follower Index] = CurrentAccount
What did you mean by activating the two relationships between 'VOLUMN FACT' and 'Account Type DIM' together?

bi_kartik · Answer

tamerj1&nbsp;What I want to do here is that,CASE 1 : when I select "provider" from account type filter the volume should show&nbsp;total volume = manual open + manual close + copytrading open + copytrading close (only for providers)similarly for when i select follower.CASE 2 : Also, when i have selected provider from account type filter and manual open volume from volume type filter the total volume should be =&nbsp; manual open (only for provider)CASE 3 : When i select only manual open from the volume type filter,the total volume should be = manual open ( for both provider and followers).I know where the problem is in my code, its just that I don't know how to solve this. let me explain it using a part of the code that I have written (manual open variable)VAR&nbsp;__Manual_open_volume&nbsp;=CALCULATE(&nbsp; &nbsp;&nbsp;CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;SUM(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'VOLUME FACT'[manual_opened_usd_volume]&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;USERELATIONSHIP(&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'VOLUME FACT'[Manual Open Volume Index],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'Volume Type DIM'[Index]&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ),&nbsp; &nbsp;&nbsp;USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'VOLUME FACT'[Provider Index],&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'Account Type DIM'[id]&nbsp; &nbsp; ),&nbsp; &nbsp;&nbsp;USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'VOLUME FACT'[Follower Index],&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'Account Type DIM'[id]&nbsp; &nbsp; ))Now, in CASE 1 : When I have provider selected, The above DAX expression does not know what to compute as both these userelationships are under one calculate.USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'VOLUME FACT'[Provider Index],&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'Account Type DIM'[id]&nbsp; &nbsp; ),&nbsp; &nbsp;&nbsp;USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'VOLUME FACT'[Follower Index],&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'Account Type DIM'[id]&nbsp; &nbsp; )Hence, the error.&nbsp;If for example, I just remove the USERELATIONSHIP code for follower from the code. The calcualtion will be okay for anything selected from volume type filter and only provider is selected from account type filter.&nbsp; &nbsp;&nbsp;USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'VOLUME FACT'[Follower Index],&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'Account Type DIM'[id]&nbsp; &nbsp; )The key is to make DAX understand that when provider is selected it needs to compute the following&nbsp;VAR __Manual_open_volume =CALCULATE(&nbsp; &nbsp;&nbsp;CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;SUM(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'VOLUME FACT'[manual_opened_usd_volume]&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;USERELATIONSHIP(&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'VOLUME FACT'[Manual Open Volume Index],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'Volume Type DIM'[Index]&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ),&nbsp; &nbsp;&nbsp;USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'VOLUME FACT'[Provider Index],&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;'Account Type DIM'[id]&nbsp; &nbsp; ))and when follower is selected DAX needs to compute the followingVAR __Manual_open_volume =CALCULATE(&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; SUM(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[manual_opened_usd_volume]&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[Manual Open Volume Index],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Volume Type DIM'[Index]&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ),&nbsp; &nbsp; USERELATIONSHIP(&nbsp; &nbsp; &nbsp; &nbsp; 'VOLUME FACT'[Follower Index],&nbsp; &nbsp; &nbsp; &nbsp; 'Account Type DIM'[id]&nbsp; &nbsp; ))Its like "IF OR" statement between the two USEREALTIONSIHPS,&nbsp;If provider then compute manual open volume for provider and if follower then compute manual open volume for follower&nbsp;

Forum Discussion

USERELATIONSHIP complex case

4 Replies

Related content

RLS and USERELATIONSHIP problem

USERELATIONSHIP problems with n:m-relationship

Sorting case

Case Sensitivity

Cases per Day Calculation