topic Re: How to avoid row interaction in a DAX in DAX Commands and Tips

How to avoid row interaction in a DAX

LaurenceA — Tue, 15 Apr 2025 19:23:50 GMT

I have made a DAX measure that calculate the sales of the last 2 years.
I use this measure in a table and filter to isolated only the customer with X$ or less. So far so Good.

But when I add the Sales reps it will return the line for each sales rep. wich I don't want. Is there a wat to avoid this.
My goal is to see which client did not buy for the last two year and did buy this year and to see the sales rape associated with the current year sales.

Here is the DAX

_InvoiceNetAmountCAD_Sum_Last2years =

VAR _ThisYear = YEAR( TODAY() ) -- Retourne l'année en cours (Ex: 2025)

VAR _LastYear = _ThisYear - 1 -- Retourne l'année dernière basé sur l'année en cours (Ex: 2024)

VAR _SecondToLastYear = _ThisYear -2 -- Retourne l'avant année dernière basé sur l'année en cours ( Ex: 2023)

VAR _NetAmountLastYear =

CALCULATE(

[_ConsolidatedInvoiceNetAmountCAD_Sum], -- Somme le Invoice Net Amount CAD

FILTER( ALL('Billing Date'),

YEAR('Billing Date'[Billing Date]) = _LastYear -- Si l'année de facturation est égale à celle de l'année dernière

)

VAR _NetAmountSecondToLastYear =

CALCULATE(

[_ConsolidatedInvoiceNetAmountCAD_Sum], -- Somme le Invoice Net Amount CAD

FILTER( ALL( 'Billing Date'),

YEAR('Billing Date'[Billing Date]) = _SecondToLastYear -- Si l'année de facturation est égale à celle d'il y a deux an

)

RETURN

_NetAmountLastYear +_NetAmountSecondToLastYear -- Retourne le Invoice Net AMount CAD des deux dernières années

Re: How to avoid row interaction in a DAX

Greg_Deckler — Tue, 15 Apr 2025 21:10:02 GMT

LaurenceA There is probably a way. However, the right answer is going to be heavily dependent on your semantic model.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: How to avoid row interaction in a DAX

v-saisrao-msft — Thu, 17 Apr 2025 11:03:52 GMT

Hi LaurenceA,

Thank you for reaching out to the Microsoft Fabric Forum Community.

It sounds like the issue arises when adding the Sales Rep field to your table. The measure you created correctly calculates sales for the last two years, but when Sales Rep is added, the visual breaks into multiple rows per customer due to the measure being re-evaluated in the context of each rep.

Below is the revised Dax Measure

_InvoiceNetAmountCAD_Sum_Last2years = VAR _ThisYear = YEAR(TODAY()) VAR _LastYear = _ThisYear - 1 VAR _SecondToLastYear = _ThisYear - 2 VAR _NetAmountLastYear = CALCULATE( [_ConsolidatedInvoiceNetAmountCAD_Sum], FILTER( ALL('Billing Date'), YEAR('Billing Date'[Billing Date]) = _LastYear ), REMOVEFILTERS('Sales Rep') ) VAR _NetAmountSecondToLastYear = CALCULATE( [_ConsolidatedInvoiceNetAmountCAD_Sum], FILTER( ALL('Billing Date'), YEAR('Billing Date'[Billing Date]) = _SecondToLastYear ), REMOVEFILTERS('Sales Rep') ) RETURN _NetAmountLastYear + _NetAmountSecondToLastYear

If this solution doesn’t fully resolve the issue or you’re seeing unexpected behavior in the visual, would you mind sharing a small sample of your dataset?

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

Re: How to avoid row interaction in a DAX

v-saisrao-msft — Mon, 21 Apr 2025 04:29:07 GMT

Hi LaurenceA,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

Re: How to avoid row interaction in a DAX

v-saisrao-msft — Thu, 24 Apr 2025 06:44:53 GMT

Hi LaurenceA,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Re: How to avoid row interaction in a DAX

v-saisrao-msft — Mon, 28 Apr 2025 04:14:42 GMT

Hi LaurenceA,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.