topic Re: Remove the Row context while keeping Filter context in DAX Commands and Tips

Remove the Row context while keeping Filter context

marcofalzone — Fri, 30 Jun 2023 15:02:24 GMT

Hi community,

I have a table like the following, where Comp_x are companies belonging to the same group (owner).

Table: "Transactions"

My goal is to write a DAX measure that calculates internal transactions (ie: transactions between my companies only, excluding third parties), and also the "Eliminations" of such internal transactions, defining an elimination as "the minimum amount between the sum of transactions of Company vs Counterparts in the same Product Line". Therefore, the sum of Elimination column must be always zero.

Examples, setting a Filter Context on "Engineering" in the report page, the expected result by Product Line on my visual should be:

In the first, the minimum is represented by the sum of Costs (-30) that remains within the same product line (purchases of Comp_2 vs Comp_1), so the amount of Internal Eliminations on Revenues of Comp_1 becomes 30 as well.

While setting a Filter Context on "Laboratories" in the report page, the expected result by Product Line on my visual should be:

In the second example, the minimum is represented by by the sum of Costs (-55) that remains within the same product line (purchases of Comp_2 vs Comp_1), but in this case Revenues are splitted in 2 kinds. Since there's no link between Revenues of Companies, and Costs of respective Counterparts, the measure should calculate the Elimination starting from the first revenues transactions, until the accumulated limit of 55 is reached.

I'm struggling to find a solution due to the presence of Filter Context and Row Context, both coexisting.

The logics behind my measures is the following:

[Internal Transactions] = "Calculates the Sum of Amount, by P&L Row, where Companies and Counterparts belong to the same filtered Product Line"

[Internal Eliminations] = "Calculates the Minimum between the Internal Transactions of each Company vs Counterpart, within she same filtered product line, and assign such minimum value (the limit) to transactions grouped by P&L Row".

Hopefully somebody could help me to figure out how to setup the measures.

Thank you.

Marco

Download Example (xlsx)

Re: Remove the Row context while keeping Filter context

sevenhills — Sat, 01 Jul 2023 01:46:58 GMT

Try this for Internal Transactions, it will match the Laboratories example, but NOT match the Engineering as I don't see the data for Engineering + Cost Personnel in your example.

Internal Transactions = var _selPL = SELECTEDVALUE(TableTransactions[Product Line]) var _selPLRow = SELECTEDVALUE(TableTransactions[P&L_Row]) RETURN CALCULATE( SUM('TableTransactions'[Amount]), FILTER( ALLSELECTED(TableTransactions), TableTransactions[Product Line] = _selPL && TableTransactions[P&L_Row] = _selPLRow && CONTAINSSTRING(TableTransactions[Company], "Comp") && CONTAINSSTRING(TableTransactions[Counterpart], "Comp") ) )

For the Internal Eliminations,

* Are you trying to show the same value for Costs* rows as Internal Transactions value?
* For Revenue Services rows, are you just negating this above calculated sum of all costs rows value?

* For the rest of the rows, you are showing as zero... Kind of not clear!

Tried some DAX to get what you need ...

Internal Eliminations = var _selPL = SELECTEDVALUE(TableTransactions[Product Line]) var _selPLRow = SELECTEDVALUE(TableTransactions[P&L_Row]) var _selCompany = SELECTEDVALUE(TableTransactions[Company]) var _selCounterpart = SELECTEDVALUE(TableTransactions[Counterpart]) var _Tmp1 = ABS( CALCULATE( SUM('TableTransactions'[Amount]), FILTER( ALL(TableTransactions), TableTransactions[Product Line] = _selPL && CONTAINSSTRING(TableTransactions[P&L_Row], "Costs") ) )) RETURN SWITCH (TRUE() , CONTAINSSTRING(_selPLRow, "Costs"), [Internal Transactions] , CONTAINSSTRING(_selPLRow, "Revenues Services"), _Tmp1 , 0)

Hope this helps or tune to your requirements!

Re: Remove the Row context while keeping Filter context

marcofalzone — Sat, 08 Jul 2023 00:27:11 GMT

Dear @sevenhills , I really appreciate your effort. Thank you very much for your kind and precise reply.
I do apologize for this late reply, I've been travelling this week. I'm going to have a deep dive into your message, and I'll be back with a feedback within this weekend.

Thanks again for your contribution, I do really appreciate!

Marco - Monterrey, Mexico.