Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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...
DAX
smpa01's avatar
smpa01
Community Champion
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    I tested this solution but unfortunately it still returns repeated id

    • smpa01's avatar
      smpa01
      Community Champion
      4 years ago

      Anonymous provide sample data and expected output.