Use a created Dax measure in Power Query or in a relational model ?

[SaaM]

Hi All,

I was wondering if is it possible to use a created Dax measure from a report in the relational model and refering to it in Power Query without creating the measure with a M code ?

The reason why I m asking this question is because when i create my measure in DAX, it takes few seconds to have the results while with power query it takes hours ....

My two formulas:

DAX:
---------------------------------------------------------------------------------
Col = IF(
	ISBLANK(
		MINX(
			Filter(
				Table,  
				and(
					Table[item] = EARLIER(Table[item]), 
					Table[index] > EARLIER(Table[index])
					)
				), 
			Table[index]
			)
		),
	Table[index],
	MINX(
			Filter(
				Table,  
				and(
					Table[item] = EARLIER(Table[item]), 
					Table[index] > EARLIER(Table[index])
					)
				), 
			Table[index]
			)
)
PowerQuery:
---------------------------------------------------------------------------------
    fn= (tbl as table, item as text, val as number) => let 
        FilterTbl = Table.SelectRows(tbl, each ([item] = item and [index] > val)),
        MinCol = if List.Min(FilterTbl[index]) is null then val else List.Min(FilterTbl[no ligne])
        in MinCol,
    #"Added Custom" = Table.AddColumn(#"AddedIndex", "Col", each fn(#"AddedIndex", [item], [index])),

I hope there is a solution for that

Thanks

kind regards

Saam

[Anonymous · Accepted solution]

To put it mildly, your way of asking for help is not one that ensures a very quick response.

But, if you are lucky, it may be that, with a lot of effort, I was able to guess your intentions. I only know something about Power Query so I can't read the DAX. In your svcript power query an undefined name [no ligne] is used, which I have interpreted as [Index] (or [Indice] in Italian). For the rest I tried to imagine what you are looking for.

If this doesn't work or works but isn't fast enough, you need to load a table with your data (maybe fake) providing size information. Number of rows, columns, different groups

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YGRSWAyGYOdiCGOKQsUiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [item = _t]),
    #"Aggiunta colonna indice" = Table.AddIndexColumn(Origine, "Indice", 1, 1, Int64.Type),
    #"Raggruppate righe" = Table.Group(#"Aggiunta colonna indice", {"item"}, {{"aaa", each Table.FromColumns({[Indice],List.Skip([Indice])&{null}})}}),
    #"Tabella aaa espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "aaa", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Ordinate righe" = Table.Sort(#"Tabella aaa espansa",{{"Column1", Order.Ascending}})
in
    #"Ordinate righe"

[mahoneypat]

I submitted that same idea a while ago (use M/DAX table functions in the other), but no can do.  If you want the column pre-calculated, would a DAX column or calculated table work?  If not, I would try using List.Buffer or Table.Buffer in your query.  If you show starting/ending tables with example data, a specific solution can be suggested.

Pat