topic Re: Convert Earlier function in DAX to Power Query (Reverse Cumulative) in DAX Commands and Tips

Convert Earlier function in DAX to Power Query (Reverse Cumulative)

Anonymous — Wed, 10 Mar 2021 01:11:39 GMT

Hello,

Apologies in advance but I cannot seem to find a solution to this question.

Basically, I wrote a DAX query that works perfectly, but i realised a Power query will be better because I need Power's pivot functionality for some required post analysis. I just started learning DAX and have no idea how to write power queries.

For the query, I created two variables _department & _project and execute the following:

CALCULATE(COUNTROWS('Table'),FILTER('Table',__department='Table'[department]),FILTER('Table',__project='Table'[Project type]),FILTER('Table','Table'[date]>=EARLIER('Table'[date]))

The query filters the table by department and project, then does a reverse cummulative count on the filtered results, grouped by dates.

Any idea would be a good starting point for me.

Thanks

Re: Convert Earlier function in DAX to Power Query (Reverse Cumulative)

CNENFRNL — Wed, 10 Mar 2021 09:52:05 GMT

Anonymous , as to your DAX formula alone, it could be simplified as

CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[department] = __department && 'Table'[Project type] = __project && 'Table'[date] >= EARLIER ( 'Table'[date] ) ) )

as to the equivalence to EARLIER(), I think you can conduct some filtering after grouping the dataset by columns [department] and [Project type]. Paste some mockup data for further explanation if you want.

Re: Convert Earlier function in DAX to Power Query (Reverse Cumulative)

Anonymous — Wed, 10 Mar 2021 16:03:18 GMT

Hey,

I have been having some challenges replying. Made several attempts for over an hour. Something about 'Post flooding detected (community received posts of a unique message more than 1 times within 3,600'

I hope this works!

My response to your question

Re: Convert Earlier function in DAX to Power Query (Reverse Cumulative)

CNENFRNL — Wed, 10 Mar 2021 16:45:02 GMT

Anonymous , here's the M code for your reference,

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ0NFTSUfIozU3MUwhKLc4vLUpOLQaKGBkYGeoaAJGxUqwONdWZIKtzy8xLzEtOxWkOFnlTCuVNyHEnHnWm5IQPIXfROlzoEI8Q+5yMiDOHsDpjctThDGfc8qYE5IHmxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Department = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Department", type text}, {"Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Project", "Department"}, {{"Count", each Table.RowCount(_), Int64.Type}}), #"Reverse Cumulative Count" = Table.AddColumn(#"Grouped Rows", "Reverse Cumulative Count", each List.Sum(Table.SelectRows(#"Grouped Rows", (x) => x[Project] = [Project] and x[Department] = [Department] and x[Date] >= [Date])[Count])) // ^^^^^^^^^^^^^^^^^ // it's equivalent to EARLIER() in #"Reverse Cumulative Count"

Re: Convert Earlier function in DAX to Power Query (Reverse Cumulative)

Anonymous — Wed, 10 Mar 2021 17:04:42 GMT

Thanks so much. Will try and out and revert. Thanks