Forum Discussion

Vietle111's avatar
Vietle111
Frequent Visitor
2 years ago

Help on DAX: Calculate, All, Selectedvalue, Userelationship

Dear community,

 

I create 2 period columns for quarter comparison over years for my accounting report. Ideally, every entry record has a pair of both Debit & Credit. So when I select an acount in Debit, then another table will show Credit - (to find its counterparty: I use DAX: <>selectedvalue)

This is my current model

I have these formulas:

This works for me: to find counterparty of The First Period (Period 1)

Counterparty =
VAR ExcludePL=SELECTEDVALUE('0-PL List'[PL Account])
RETURN
CALCULATE(sum('2. PL Entry'[Base Amount]),FILTER('2. PL Entry','2. PL Entry'[Account Code]<>ExcludePL),
TREATAS(VALUES('0-PL Period'[Period]),'2. PL Entry'[Period]))
 
This doesn't work: I am trying to find counterparty for Second Period (period 2)
Counterparty Pre =
VAR ExcludePL=SELECTEDVALUE('0-PL List'[PL Account])
RETURN
CALCULATE(SUM('2. PL Entry'[Base Amount]),ALL('0-PL Period'),USERELATIONSHIP('0-PL Period Compare'[Period],'2. PL Entry'[Period]),'2. PL Entry'[Account Code]<>ExcludePL)

 I tried different ways to write DAX for second period: result can be 0 or dupicate each value of subtotal. Please help me to review above formula.

Thanks.

 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Vietle111 ,

    Based on the description, please try the following DAX formula:

     

    Counterparty Pre =
VAR ExcludePL = SELECTEDVALUE('0-PL List'[PL Account])
RETURN
CALCULATE(
    SUM('2. PL Entry'[Base Amount]),
    FILTER(
        ALL('2. PL Entry'),
        '2. PL Entry'[Account Code] <> ExcludePL
    ),
    USERELATIONSHIP('0-PL Period Compare'[Period], '2. PL Entry'[Period])
)

     

    View the following documents to learn about dax function.

    ALL function (DAX) - DAX | Microsoft Learn

    USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

     

    If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format to make a deep troubleshooting? It would be helpful to find out the solution.

    You can refer the following links to share the information:

    How to provide sample data in the Power BI Forum

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Vietle111's avatar
      Vietle111
      Frequent Visitor
      2 years ago

      Hi Wu,

      I tried the fomular you provided but it returns blank instead. Figuring out the way to provide dataset after removing all sensitive data.
      I'll back to you soon.

      Thanks.