topic Re: How to replicate an excel formula in Power BI that self refers the column in DAX Commands and Tips

How to replicate an excel formula in Power BI that self refers the column

Gui_silva — Mon, 03 Oct 2022 16:19:34 GMT

Hi,

I have written this formula in excel that is self referred, however in power BI I can't self refer the measure or column that I try to create. The formula is: =IFERROR(IF(B2=0;0;IF(B2+C1>7;1;B2+C1));B2)

Does anyone know a solution for this?

Thanks in advance!

Gui 😃

Re: How to replicate an excel formula in Power BI that self refers the column

Greg_Deckler — Mon, 03 Oct 2022 16:23:38 GMT

Gui_silva More than likely not going to happen. Previous Value (“Recursion”) in DAX – Greg Deckler

What are you trying to achieve?

Re: How to replicate an excel formula in Power BI that self refers the column

Gui_silva — Mon, 03 Oct 2022 16:34:56 GMT

Read your post, now I am aware of this limitation.

I was trying to achieve the same resulta that I get in excel. If I receive 1 Tip in a sequence of days, it gets accumulated up to 7, and then returns to 1 if a receive 1 Tip that day or 0 if I dont receive 1 Tip. If I have a break in the sequence of receiving Tips (a day with 0) then it returns to 0. Not sure, if I was clear.

Thanks for replying!

Re: How to replicate an excel formula in Power BI that self refers the column

Greg_Deckler — Mon, 03 Oct 2022 16:39:57 GMT

Gui_silva There may be hope then: Cthulhu - Microsoft Power BI Community. If you post sample data as text, I can be more specific.

Re: How to replicate an excel formula in Power BI that self refers the column

Gui_silva — Mon, 03 Oct 2022 17:28:21 GMT

Here's a sample:

DATA	TIP
02/05/2022	1
04/05/2022	1
06/05/2022	0
08/05/2022	1
10/05/2022	0
12/05/2022	1
14/05/2022	0
16/05/2022	0
18/05/2022	0
20/05/2022	1
22/05/2022	1
24/05/2022	1
26/05/2022	1
28/05/2022	1
30/05/2022	1
01/06/2022	1
03/06/2022	1
05/06/2022	0
07/06/2022	1
09/06/2022	0
11/06/2022	1
13/06/2022	1
15/06/2022	1
17/06/2022	1
19/06/2022	1
21/06/2022	1
23/06/2022	1
25/06/2022	1
27/06/2022	1
29/06/2022	1

Re: How to replicate an excel formula in Power BI that self refers the column

tamerj1 — Mon, 03 Oct 2022 18:07:17 GMT

Hi Gui_silva
Please refer to attached sample file with the solution

Accumulated Tips = VAR FirstData = MIN ( 'Table'[DATA] ) VAR CurrentData = 'Table'[DATA] VAR TableBefore = FILTER ( 'Table', 'Table'[DATA] <= CurrentData ) VAR FilteredTable1 = FILTER ( TableBefore, 'Table'[TIP] = 0 ) VAR MinData = MAXX ( FilteredTable1, 'Table'[DATA] ) VAR FilteredTable2 = FILTER ( TableBefore, 'Table'[DATA] > MinData && 'Table'[DATA] > FirstData ) VAR AccumulatedValue = SUMX ( FilteredTable2, 'Table'[TIP] ) VAR Result1 = MOD ( AccumulatedValue, 7 ) VAR Result2 = IF ( Result1 > 0, Result1, IF ( ISBLANK ( Result1 ), 0, 7 ) ) RETURN IF ( FirstData = CurrentData, 'Table'[TIP], Result2 )

Re: How to replicate an excel formula in Power BI that self refers the column

Gui_silva — Mon, 03 Oct 2022 18:25:11 GMT

Thank you so much tamerj1.

I hope to get at this level at some point haha!