topic Re: Help on DAX: Calculate, All, Selectedvalue, Userelationship in DAX Commands and Tips

Help on DAX: Calculate, All, Selectedvalue, Userelationship

Vietle111 — Thu, 11 Apr 2024 09:28:37 GMT

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.

Re: Help on DAX: Calculate, All, Selectedvalue, Userelationship

Anonymous — Fri, 12 Apr 2024 02:17:31 GMT

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.

Re: Help on DAX: Calculate, All, Selectedvalue, Userelationship

Vietle111 — Tue, 16 Apr 2024 11:46:25 GMT

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.

Re: Help on DAX: Calculate, All, Selectedvalue, Userelationship

Vietle111 — Wed, 17 Apr 2024 15:25:06 GMT

Hi Wu,

I created a copy of data, so please help me to revise Dax fomular Counterparty of Previous period. Thanks
https://www.dropbox.com/t/o3pPuqpTKGBF4uR5