More efficient method to check last 4 rows of data in DAX for calculated column

[s1]

Hello,

I guess my question is:

• Is my DAX code written in an obviously inefficient way?
• If yes, is there a better way to do it?

I created a DAX calculated column to flag in which weeks a company is "Engaged", which is defined as:

• The company has performed at least 1 action per week
• For at least 3 of the last 4 weeks

I wrote the code below to achieve this. However, it seems to be incredibly slow. Before creating this column, it takes 1 second to add a new calculated column to the Company Events table. After creating this column, it takes about 30 seconds to do the same thing. The table is currently 14,000 rows, but I have example data below.

Engaged = 
VAR company = 'Company Events'[Company Name]
VAR week = 'Company Events'[Weeks since Purchase]
RETURN
IF(
    CALCULATE(
        DISTINCTCOUNT('Company Events'[Weeks since Purchase]),
        ALL('Company Events'),
        'Company Events'[Company Name] = company,
        'Company Events'[Weeks since Purchase] <= week,
        'Company Events'[Weeks since Purchase] >= week - 3,
        'Company Events'[Actions] > 0
    ) >= 3,
    1,
    0
)

Example table with desired result in blue.

Company Name	Date (week)	Weeks since Purchase	Actions	Engaged
Company A	2022-09-04	15	5	0
Company A	2022-09-11	16	0	0
Company A	2022-09-18	17	3	0
Company A	2022-09-25	18	12	1
Company A	2022-10-02	19	0	0
Company A	2022-10-09	20	0	0
Company B	2022-09-04	3	2	0
Company B	2022-09-11	4	3	0
Company B	2022-09-18	5	2	1
Company B	2022-09-25	6	4	1
Company B	2022-10-02	7	0	1
Company B	2022-10-09	8	0	0
Company C	2022-09-18	0	11	0
Company C	2022-09-25	1	7	0
Company C	2022-10-02	2	9	1
Company C	2022-10-09	3	2	1

Thanks in advance!

additional notes:

I need this to be a calculated column and not a measure, because I am building several other calculated columns off of it (in a related company table).

I also cannot easily do this in Power Query, because "Actions" is a calculated column that relies on several relationships to calculate.

[Anonymous]

Hi  @s1 ,

Whether your problem has been resolved? If yes, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[s1 · Accepted solution]

Sorry, the suggestions didn't help resolve the problem. I ended up having to move the work into PowerQuery to get my performance back. (I suspect there may have been a circular reference issue with calculated columns between tables)
Should I mark my reply to you here as the solution, so that it isn't sitting as open?

[Anonymous]

Hi @s1 ,

Thanks for your reply. Yes, you can mark your reply as the solution if your problem has been resolved. Thank you.

Best Regards

[Anonymous]

Hi @s1 ,

Please update the formula of the calculated column [Engaged] as below and check if it run faster...

Engaged =
VAR company = 'Company Events'[Company Name]
VAR week = 'Company Events'[Weeks since Purchase]
VAR wpcount =
    CALCULATE (
        DISTINCTCOUNT ( 'Company Events'[Weeks since Purchase] ),
        'Company Events',
        'Company Events'[Company Name] = company,
        'Company Events'[Weeks since Purchase] <= week,
        'Company Events'[Weeks since Purchase] >= week - 3,
        'Company Events'[Actions] > 0
    )
RETURN
    IF ( wpcount >= 3, 1, 0 )

In addition, you can refer the following blog to optimize your DAX formula.

Improve Power BI Performance by Optimizing your DAX

Best Regards

[amitchandak]

@s1 , better to use Date table and week rank

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures

Last 4 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))