topic Re: SQL to DAX conversion in DAX Commands and Tips

SQL to DAX conversion

Anonymous — Fri, 14 Jan 2022 11:13:35 GMT

i am facing issue while converting below SQL query into DAX. Can you please help me to convert the query:

Select Count ( Distict id)

From

(Select id, ROW_NUMBER () OVER (PARTITION BY id ORDER BY id_color, id_num, id ) R

From tablle_color ) Q

WHERE R = 1

Re: SQL to DAX conversion

amitchandak — Fri, 14 Jan 2022 11:57:54 GMT

Anonymous , You have to create a column first

R = rankx(filter(tablle_color, [id_color] = earlier([id_color]) && [id_num] = earlier([id_num])), [id],,asc,dense)

The create a measure

measure = calculate( distinctcount(Table[ID]) , filter( tablle_color, tablle_color[R]=1))

Re: SQL to DAX conversion

smpa01 — Fri, 14 Jan 2022 15:56:05 GMT

AlexisOlson CNENFRNL bcdobbs parry2k this is an interesting problem to me and let me describe it here.

TLDR :- is there any way to pass on multiple column expression in RANKX <expression>

RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])

OP's SQL (before filtering R=1) can be simplified as

Select id, id_color, id_num, ROW_NUMBER () OVER ( PARTITION BY id ORDER BY id_color, id_num, id ) as Row # From @tabelle_color

With a SQL DDL it will look like this

declare @tabelle_color as table (id varchar(1), id_color varchar(10), id_num integer,[index] integer) insert into @tabelle_color select * from (values('1','red',1,1), ('1','green',1,2), ('1','magenta',2,3) , ('2','purple',-1,4), ('2','blue',-1,5), ('2','orange',2,6) ) t (a,b,c,d) --Select id, id_color, id_num, [index], ROW_NUMBER () OVER (PARTITION BY id ORDER BY id_color, id_num, id ) as Row# -- From @tabelle_color -- order by id, [Row#] ASC Select id, id_color, id_num, [index], ROW_NUMBER () OVER (PARTITION BY id ORDER BY id_num, id_color, id ) as Row# From @tabelle_color order by id, [Row#] ASC

The reason why it is interesting to me is because SQL ORDER BY takes the combination of the first column and second column specified in ORDER BY to generate the ROW_Number(). To prove my point, I changed the ORDER BY

ORDER BY id_num, id_color

as opposed to OP's

ORDER BY id_color, id_num

In DAX while it is no issue at all to replicate PARTITION (with many many partitions if req), I have rarely come across a situation to generate ranking based on two columns inside a PARTITION, which is why this is a good opportunity to discuss it here.

This is what my approach is and can you please take a look and advise if this is the correct approach to pass on multiple columns for ORDER BY Inside RANKX in <expression> that is equivalent of SQL Order by.

RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])

Measure = RANKX ( FILTER ( ALLSELECTED ( _tbl ), _tbl[id] = CALCULATE ( MAX ( _tbl[id] ) ) ), CALCULATE ( MAX ( _tbl[id_num] ) ) & CALCULATE ( MAX ( _tbl[id_color] ) ), , ASC, DENSE )

I could not make it to work

However, I can make the calculated column as following and I can also write an equivalent table expression.

But how can an equivalent measure be written?

Re: SQL to DAX conversion

AlexisOlson — Fri, 14 Jan 2022 16:08:40 GMT

Simple concatenation of columns can get you into trouble since e.g. "2blue" > "10pink".

For ranking over multiple columns, I'd start here:
https://www.sqlbi.com/articles/rankx-on-multiple-columns-with-dax-and-power-bi/

Re: SQL to DAX conversion

parry2k — Fri, 14 Jan 2022 16:19:09 GMT

smpa01 and this might work:

Measure = RANKX ( FILTER ( ALLSELECTED ( _tbl ), _tbl[id] = ( MAX ( _tbl[id] ) ) ), CALCULATE ( MAX ( _tbl[id_num] ) & MAX ( _tbl[id_color] ) ), , ASC, DENSE )

Re: SQL to DAX conversion

smpa01 — Fri, 14 Jan 2022 16:42:38 GMT

parry2k Wow !!! Thanks, this is awesome.

Re: SQL to DAX conversion

smpa01 — Fri, 14 Jan 2022 17:09:55 GMT

Anonymous you can use a measure like this

Measure = VAR _rank = RANKX ( FILTER ( ALLSELECTED ( _tbl ), _tbl[id] = MAX ( _tbl[id] ) ), CALCULATE ( MAX ( _tbl[id_num] ) & MAX ( _tbl[id_color] ) ), , ASC, DENSE ) VAR _filt = FILTER ( ADDCOLUMNS ( _tbl, "rank", [_rank] ), [rank] = 1 ) RETURN CALCULATE ( DISTINCTCOUNT ( _tbl[id] ), _filt )

Re: SQL to DAX conversion

AlexisOlson — Fri, 14 Jan 2022 17:21:45 GMT

Anonymous Can you explain what this query is intended to do? I don't quite follow how the ordering going on has any effect on the distinct count of id.

Re: SQL to DAX conversion

CNENFRNL — Fri, 14 Jan 2022 20:43:55 GMT

IMHO, ROW_NUMBER() has nothing to do with RANKX(); its equivalent is Table.Group(..., {Table.AddIndexColumn()}) in PQ,

DECLARE @tabelle_color AS TABLE ( [index] INTEGER IDENTITY(1, 1), id NVARCHAR(1), id_color NVARCHAR(10), id_num INTEGER ) INSERT INTO @tabelle_color (id, id_color, id_num) VALUES ('1', 'red', 3), ('2', 'blue', 1), ('1', 'red', 1), ('1', 'magenta', 2), ('2', 'purple', -1), ('1', 'green', 1), ('2', 'blue', -1), ('2', 'orange', 3), ('1', 'red', 6), ('1', 'green', 5), ('2', 'orange', 2), ('1', 'red', 1), ('1', 'red', 4) SELECT id, id_color, id_num, Row_number () OVER ( PARTITION BY id ORDER BY id_num, id_color ) AS [Row #] FROM @tabelle_color ORDER BY [id]

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY/dCoAgDIXfZdcFrezvWcQLo+FNmUi9f6lDULqYjM+zszMpAaGJ5Wn/3gFUI6H/ulDb8VD8DnAohIkJZqc2ZG8dxwIf2cA93h3Bok36ifXGE9nsMpfrWLswvby2hnK2tYgxRYZd5TsmjLVFSof9zylY3idAqRc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [index = _t, id = _t, id_color = _t, id_num = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"index", Int64.Type}, {"id", Int64.Type}, {"id_color", type text}, {"id_num", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {"all", each Table.AddIndexColumn(Table.Sort(_, {{"id_num", Order.Ascending}, {"id_color", Order.Ascending}}), "# Row", 1)}), #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"id_color", "id_num", "# Row"}, {"id_color", "id_num", "# Row"}) in #"Expanded all"

All calculation of [#ROW] is conducted under the NON-FILTERING circumstances, whereas RANKX is extremely subject to evaluation context.

Re: SQL to DAX conversion

smpa01 — Fri, 14 Jan 2022 20:51:50 GMT

CNENFRNL yes you are absolutely right. ROW_NUMBER() and RANK() are two different windowed functions in SQL. The closest to ROW_NUMBER() what we have in DAX is RANKX(). That is what I meant.

Thanks again !!! learning a lot from here everyday.

Re: SQL to DAX conversion

Anonymous — Mon, 17 Jan 2022 08:41:48 GMT

I tested this solution but unfortunately it still returns repeated id

Re: SQL to DAX conversion

smpa01 — Mon, 17 Jan 2022 13:45:42 GMT

Anonymous provide sample data and expected output.

Re: SQL to DAX conversion

Anonymous — Mon, 17 Jan 2022 14:00:11 GMT

I did, but it gave me a very small number. In my table I have 33 thousand records and with the measurement it gave me only 176.