Forum Discussion
SQL to DAX conversion
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
12 Replies
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))
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_colorWith 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#] ASCThe 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
to
ORDER BY id_num, id_coloras opposed to OP's
ORDER BY id_color, id_numIn 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?
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/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.
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 )Anonymous provide sample data and expected output.
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.
