cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AGo Member
Member

Converting DAX to Power Query M language in the query editor

Column = 
CALCULATE (
    MAX ( Table1[date] ),
    FILTER (
        ALL ( Table1 ),
        EARLIER ( Table1[product ID] ) = Table1[product ID]
            && EARLIER ( Table1[date] ) <= Table1[date]
            && EARLIER ( Table1[Index] ) <> Table1[Index]
    )
)

 

Hi,

I have this DAX formula that blows up the server (13GB RAM) on a 400K rows database and I'd like to have it in M language and copy it in the query editor to test if it works faster or being less RAM consuming. Can you please help me?

2 REPLIES 2
dchager Frequent Visitor
Frequent Visitor

Re: Converting DAX to Power Query M language in the query editor

Read www.daxpatterns.com to find a more efficient DAX formula.

Moderator Eric_Zhang
Moderator

Re: Converting DAX to Power Query M language in the query editor

Capture.PNG@AGo

 

Not an expert on Power Query and doubt it would have much better performance than DAX. As you're using database, if SQL Server, try an equivalent query as below.

 

SELECT *
FROM table1 t1
CROSS APPLY (
	SELECT max([date]) f
	FROM table1 t2
	WHERE t1.[product ID] = t2.[product ID]
		AND t1.[Index] <> t2.[Index]
		AND t1.[date] <= t2.[date]
	) ca

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)