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

In "calculate" use suitable function like sumx, countx on [TotalAmountGLEntry] and the move filter in the first parameter of table name and try.

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

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

    Dear amitchandak 

    Thanks for your answer. 

    I'm not sure I understood exactly what you mean. 

     

    I tried this, but it didn't work:

    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 ()
        )
    );
    SUM('$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

      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])
)