Search code examples
sqlpowerbidaxrankrow-number

How to convert SQL query to Power BI DAX query?

Desired Output

Hi, i want to convert my sql query to a DAX measures for to filter all that have Rank 1 only. I've been trying to use RankX(Filter) but I get duplicate ranks. Thank you

row_number() over (partition by date,customer, type order by day)

 
┌──────────┬──────────┬─────┬──────┬──────────────┬
│ Day      │ Customer │ Day │ Type │ Desired Rank │
├──────────┼──────────┼─────┼──────┼──────────────
│ 6/1/2020 │ DDD      │   1 │ D    │            1 │
│ 6/1/2020 │ DDD      │   0 │ M    │            1 │
│ 6/1/2020 │ DDD      │   1 │ D    │            2 │
│ 6/1/2020 │ DDD      │   0 │ M    │            3 │
│ 6/1/2020 │ FFF      │   0 │ M    │            1 │
│ 6/1/2020 │ FFF      │   0 │ M    │            2 │
│ 6/1/2020 │ FFF      │   0 │ M    │            3 │
│ 6/1/2020 │ FFF      │   0 │ M    │            4 │
│ 6/1/2020 │ BBB      │   1 │ D    │            1 │
│ 6/1/2020 │ BBB      │   0 │ M    │            1 │
│ 6/1/2020 │ BBB      │ 0   │ M    │            2 │ 
│ 6/1/2020 │ BBB      │ 0   │ M    │            3 │
│ 6/2/2020 │ FFF      │ 2   │ D    │            1 │
│ 6/2/2020 │ BBB      │ 2   │ D    │            1 │
│ 6/3/2020 │ FFF      │ 3   │ D    │            1 │ 
│ 6/3/2020 │ FFF      │ 3   │ D    │            2 │
│ 6/3/2020 │ FFF      │ 3   │ D    │            3 │
│ 6/4/2020 │ DDD      │ 4   │ D    │            1 │
│ 6/4/2020 │ DDD      │ 4   │ D    │            2 │
│ 6/4/2020 │ DDD      │ 4   │ D    │            3 │
│ 6/4/2020 │ FFF      │ 4   │ D    │            1 │
│ 6/4/2020 │ FFF      │ 4   │ D    │            2 │
│ 6/5/2020 │ EEE      │ 5   │ D    │            1 │
│ 6/5/2020 │ EEE      │ 5   │ D    │            2 │
│ 6/5/2020 │ DDD      │ 5   │ D    │            1 │
│ 6/5/2020 │ DDD      │ 5   │ D    │            2 │
│ 6/5/2020 │ DDD      │ 5   │ D    │            3 │
│ 6/5/2020 │ FFF      │ 5   │ D    │            1 │
└─────────────────────┴─────┴──────┴──────────────┘
Solution

  • Follow this following steps to achieve your required output.

    Step-1: Go to Power Query Editor and Add a index column to your table as shown in the below image. This is required as you have same value in multiple row for date,customer, type combination. I tried but found unexpected RANKX is generating for having same value in all rows for a group. For this reason Index column is created.

    Index column is created to keep different value in each row just. No other purpose is there for this column.

    enter image description here

    Step-2: Get back to your report by clicking "Close & Apply" button.

    Step-3: Now create a custom column in your table with this following code-

    group_wise_rank = 

RANKX (
    FILTER (
        'your_table_name',
        'your_table_name'[Date] = EARLIER ('your_table_name'[Date])
            && 'your_table_name'[Customer] = EARLIER ('your_table_name'[Customer])
            && 'your_table_name'[Type] = EARLIER ('your_table_name'[Type])
    ),
    'your_table_name'[Index],
    ,
    ASC
)

    This tricks should work for you.