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

问题描述

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

row_number()结束(按日期，客户，按日期键入订单)

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

 选择*，row_number()以上(按日期，客户，产品订单按性别划分)从 (选择"2018-01-01"作为日期"，选择"1234"作为客户"，"P2"作为产品，"M"性别联盟选择'2018-01-01'作为Date，1234，'P2'，'F'UNION选择'2018-01-03'作为Date，1235，'P1'，'F'UNION选择'2018-01-03'作为Date，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屋！