topic Re: Translate Qlik aggr expression to DAX? in DAX Commands and Tips

Translate Qlik aggr expression to DAX?

Sellgren7 — Fri, 07 Oct 2022 16:38:47 GMT

Hello

Is it possible to translate below expression to DAX or is there another way of achieving the outcome in PBI? I want to translate a table from Qlik into a matrix visual but i cant figure out how to get the aggr equivalent.

timestamp(
min({<ColumnX -= {0}>}aggr(
min({<ColumnX -= {0}>} ColumnN,', ')
,ColumnZ,ColumnY, ColumnX))
)

BR Tommy

Re: Translate Qlik aggr expression to DAX?

lazurens2 — Sun, 09 Oct 2022 20:08:33 GMT

The Qlik aggr function, aggregates a field by a set of fields, I will try to translate into natural language your measure but it's better to provide a expected result to be more accurate

timestamp( -- format into time stamp
min( -- get the minimum value of ..

{<ColumnX -= {0}>} -- set analysis to filter out ColumnX = 0,

aggr(
min({<ColumnX -= {0}>} ColumnN,', ')
,ColumnZ,ColumnY, ColumnX)) -- we get field ColumnN, where row of ColumnX = 0 are filtered out aggregated by ColumnZ,ColumnY, ColumnX
)

if I try to translate into dax these components :

AggMinValueMeasure = VAR AggrMin = SUMMARIZE ( ADDCOLUMNS( CALCULATETABLE ( SUMMARIZE ( 'Table', 'Table'[Column1], 'Table'[Column2] ), 'Table'[Column1] <> 0 ) , "Column1-1", 'Table'[Column1], "Column2-2",'Table'[Column2] ), "_Min", Min('Table'[Column2]) ) Return AggrMin

And then format the measure into timestamp in the graphical UI.

Hope this helps.

Re: Translate Qlik aggr expression to DAX?

Sellgren7 — Mon, 10 Oct 2022 10:50:39 GMT

Thanks for the quick reply, but i dont fully understand how this will aggregate over several columns? I only see Column1 = ColumnX and Column2 = ColumnN.