Count all previous Rows (Change formula from DAX to M)

[manuellchavez]

Hello,

I am trying to transform the following formula from DAX to M language, but I am not able to set the "<=" operator, can someone guide me? 

Index = CALCULATE(COUNT(Index[date]),FILTER(Index,Index[date]<=EARLIER(Index[date]) && Index[AssetId]=EARLIER(Index[AssetId]) && Index[portID]=earlier(Index[portID])))

[v-lid-msft · Accepted solution]

Hi @manuellchavez ,

We can have two solution to archieve your requirement:

1. use the group by and add index column

full M Query is here 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYiMDQ0t9Q31DpVgdDEEjuKATNkFnhKAxWNAIm3YjVO3G2ARN4YLOKE6KBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [AssetId = _t, portID = _t, date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AssetId", Int64.Type}, {"portID", type text}, {"date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"AssetId", "portID"}, {{"Data", each _, type table [AssetId=number, portID=text, date=date]}}),
    #"Added Index" = Table.AddColumn(#"Grouped Rows","New",each Table.AddIndexColumn([Data],"Index",1,1)),
    #"Expanded New" = Table.ExpandTableColumn(#"Added Index", "New", {"AssetId", "portID", "date", "Index"}, {"New.AssetId", "New.portID", "New.date", "New.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded New",{"AssetId", "portID", "Data"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"New.AssetId", "AssetId"}, {"New.portID", "portID"}, {"New.date", "date"}, {"New.Index", "Index"}})
in
    #"Renamed Columns"

add a GroupBy Clomun

then create a new column add index for each table in New column and expend it 

Remove the unnessary column and rename the extended column

2. just add a simple column

Full M Query is here:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYiMDQ0t9Q31DpVgdDEEjuKATNkFnhKAxWNAIm3YjVO3G2ARN4YLOKE6KBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [AssetId = _t, portID = _t, date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"AssetId", Int64.Type}, {"portID", type text}, {"date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type" ,"Custom", 
each let a = [AssetId],
p = [portID], 
d = [date] 
in Table.RowCount(Table.SelectRows(#"Changed Type",each [AssetId]=a and [portID]=p and [date]<=d)))
in
#"Added Custom"

If it doesn't meet your requirement, Please show the exact expected result based on the Tables above.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[manuellchavez]

Thank you very much for the detailed explanations. They both are doing what I wanted. I will try it on my file.