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

     

     

     

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
6 years ago

Hi setis 

 

Try this, if not working try without KEEPFILTERS

Membership Fee = 
CALCULATE (
    [TotalAmountGLEntry];
    KEEPFILTERS (
        FILTER (
            CALCULATETABLE(
                ALL (
                    '$G_L Entry'[G_L Account No_];
                    '$G_L Entry'[Document Type];
                    '$G_L Entry'[CUSTOMER]
                );
                USERELATIONSHIP ( '$G_L Entry'[Posting Date]; 'Date'[Date] )
            );
            '$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 ()
        )
    )
)
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

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

Mariusz Thanks again!

 

Unfortunately it didn't work either with or without KEEPFILTERS. I'm getting lines for all different G_L Account No_

  • setis's avatar
    setis
    Icon for Post Partisan rankPost Partisan
    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