Forum Discussion

setis's avatar
setis
Icon for Post Partisan rankPost Partisan
6 years ago
Solved

USERELATIONSHIP with several conditions

Dear experts, I am struggling with a measure in which I'm trying to combine USERELATIONSHIP and several filters.   My measure is thefollowing: Membership Fee= CALCULATE ( [TotalAmountGLEntry]...
Need Help
  • setis's avatar
    setis
    6 years ago

    Mariusz and amitchandak 

     

    It worked for me doing:

     

     

     

     

    Membership Fee = 
VAR Acc3011 = 
CALCULATE (
    [TotalAmountGLEntry];
    USERELATIONSHIP ( '$G_L Entry'[Posting Date]; 'Date'[Date] );
    USERELATIONSHIP('$G_L Entry'[CUSTOMER];'All Customers'[No_]);
    KEEPFILTERS ('$G_L Entry'[G_L Account No_] = "3011");
    KEEPFILTERS ('$G_L Entry'[Document Type] = 2);
    KEEPFILTERS('$G_L Entry'[CUSTOMER] <> BLANK())

        )
VAR Acc3016 = 
CALCULATE (
    [TotalAmountGLEntry];
    USERELATIONSHIP ( '$G_L Entry'[Posting Date]; 'Date'[Date] );
    USERELATIONSHIP('$G_L Entry'[CUSTOMER];'All Customers'[No_]);
    KEEPFILTERS ('$G_L Entry'[G_L Account No_] = "3016");
    KEEPFILTERS ('$G_L Entry'[Document Type] = 2);
    KEEPFILTERS('$G_L Entry'[CUSTOMER] <> BLANK())

        )
RETURN
Acc3011+Acc3016

     

     

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

Try Like. No need sum inside sumx, unless there is a need. Also ,check if it can work without keep filter.

 

Membership Fee = 
CALCULATE (
    SUMX(       
         KEEPFILTERS (
        FILTER (
            ALL (
                '$G_L Entry'[G_L Account No_];
                '$G_L Entry'[Document Type];
                '$G_L Entry'[CUSTOMER]
            );
            '$G_L Entry'[G_L Account No_] = "3011"
                || '$G_L Entry'[G_L Account No_] = "3016"
                || '$G_L Entry'[Document Type] = 2
                || '$G_L Entry'[CUSTOMER] <> BLANK ()
        )
    );
    ('$G_L Entry'[Amount]));USERELATIONSHIP('$G_L Entry'[Posting Date];'Date'[Date])
)

. 

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

If this can work

Membership Fee = 
CALCULATE (
    SUMX(       
         
        FILTER (
            '$G_L Entry';
            '$G_L Entry'[G_L Account No_] = "3011"
                || '$G_L Entry'[G_L Account No_] = "3016"
                || '$G_L Entry'[Document Type] = 2
                || '$G_L Entry'[CUSTOMER] <> BLANK ()
        )
    ;
    ('$G_L Entry'[Amount]));USERELATIONSHIP('$G_L Entry'[Posting Date];'Date'[Date])
)
  • setis's avatar
    setis
    Icon for Post Partisan rankPost Partisan
    6 years ago

    Dear amitchandak 
    In this one: 

    Membership Fee = 
CALCULATE (
    SUMX(       
         
        FILTER (
            '$G_L Entry';
            '$G_L Entry'[G_L Account No_] = "3011"
                || '$G_L Entry'[G_L Account No_] = "3016"
                || '$G_L Entry'[Document Type] = 2
                || '$G_L Entry'[CUSTOMER] <> BLANK ()
        )
    ;
    ('$G_L Entry'[Amount]));USERELATIONSHIP('$G_L Entry'[Posting Date];'Date'[Date])
)


    USERELATIONSHIP works but the filters do not. 

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

       If possible please share a sample pbix file after removing sensitive information.
      Thanks