按到 POWER BI DAX 查询的行号分区 [英] Row number partition by to POWER BI DAX query

问题描述

有人可以帮我将 sql 字符串转换为 Dax 吗?

row_number() p over(按日期、客户、按天排序)

我将输出与等效的 SQL 进行了对比.

选择*,row_number() over(按日期、客户、按性别划分的产品订单)从 (选择2018-01-01"作为日期，1234 作为客户，P2"作为产品，M"性别联盟选择 '2018-01-01' 作为日期，1234，'P2'，'F' UNION选择 '2018-01-03' 作为日期，1235，'P1'，'F' UNION选择'2018-01-03'作为日期，1235，'P2'，'F')t1

Can someone help me to convert the sql string to Dax?

row_number() p over (partition by date, customer, type order by day)enter image description here

The row number is my desired output.

解决方案

Assuming that your data looks like this table:

Sample
+------------+----------+---------+--------+
|    Date    | Customer | Product | Gender |
+------------+----------+---------+--------+
| 01/01/2018 |     1234 | P2      | F      |
| 01/01/2018 |     1234 | P2      | M      |
| 03/01/2018 |     1235 | P1      | F      |
| 03/01/2018 |     1235 | P2      | F      |
+------------+----------+---------+--------+

I have created a calculated column called Rank, using the RANKX and FILTER function.

The first part of the calculation is to create variables outside the scope of the FILTER function. The second part uses RANKX that takes an expression value - in this case Gender - to order the values.

Rank = 
VAR _currentdate = 'Sample'[Date]
VAR _customer = 'Sample'[Customer]
var _product = 'Sample'[Product]

return
RANKX(FILTER('Sample',
[Date]=_currentdate &&
[Customer] = _customer &&
[Product] = _product),[Gender],,ASC)

The output is

I contrasted the output to the SQL equivalent.

select 
*, 
row_number() over(partition by Date,Customer,Product order by Gender) 
from (
select '2018-01-01' as Date,1234 as CUSTOMER,'P2' AS PRODUCT, 'M' Gender union
select '2018-01-01' as Date,1234,'P2','F' UNION
select '2018-01-03' as Date,1235,'P1','F' UNION
  select '2018-01-03' as Date,1235,'P2','F' 
)t1

这篇关于按到 POWER BI DAX 查询的行号分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！