SQL to DAX conversion

[Anonymous]

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

[AlexisOlson]

@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.

[smpa01]

@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]

I tested this solution but unfortunately it still returns repeated id

[smpa01]

@Anonymous provide sample data and expected output.

[parry2k]

@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
)

[smpa01]

@parry2k  Wow !!! Thanks, this is awesome.

[smpa01]

@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 

to

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?

[CNENFRNL]

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.

[smpa01]

@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.

[AlexisOlson]

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/

[amitchandak]

@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))

[Anonymous]

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.