在 excel (DAX) 中查找相对于当前行值的行 [英] Find rows relative to current row's value in excel (DAX)

问题描述

有没有办法使用 DAX 根据当前行的值过滤行(我使用的是 power pivot)?

换句话说，如果我有一个表进度"，其中ID"在每一行中递增，还有一个百分比"列和另一个包含

的表

我想创建一个名为 old百分比 = (progress[ID] -1) 的百分比

这在excel中可行吗?

我在这里面找不到任何直接的命令，如果有的话请原谅，我还是个新手.

我的做法可能是创建一个新列 old ID = progress[ID] -1 然后在 power pivot 中创建一个新表，该表是当前表的副本，但随后我将其与旧 ID 链接而不是当前 ID.然后最后我做了一个旧的百分比列 = RELATED([percentage]);

这是解决问题的有效方法吗?这可以进一步优化吗?

谢谢.

我添加了一张图片来帮助显示我需要的内容

)

解决方案

JShmay,

如果您使用 的文章.可能会有帮助.

Is there a way to filter rows based on your current row's value using DAX (I'm using power pivot) ?

In other words, If I had a table "progress" with 'ID' that is incremented in each row, and a 'Percentage' columns and another table containing

I want to create a column called old percentage = percentage of (progress[ID] -1)

Is this possible in excel ?

I couldn't find any straightforward command in this, and excuse me if there were, I am still new to power pivot.

My way of doing it is perhaps to create a new column old ID = progress[ID] -1 then create a new table in power pivot which is a duplicate of the current table but then I link it with Old ID instead of the current ID. Then in the end I do a old percentage column = RELATED([percentage]);

Is this a valid approach towards the problem ? And can this be further optimized ?

Thank you.

EDIT: I've added an image to help display what I need

)

解决方案

JShmay,

the solution could be much simpler if you use combination of SUMX function and EARLIER.

Simply add a new calculated column with this formula:

=SUMX (
    FILTER ( Progress, EARLIER ( [ID] ) = [ID] + 1 ),
    Progress[PERCENTAGE]
)

EARLIER returns the current row, so if you compare it with the previous one, it can then return the correct value. This approach might not be very intuitive, but it's much more efficient and could save you lot of time.

The output should then look like this:

Try playing around to achieve the desired result, but I believe this is the simplest and most efficient approach (as far as processing time goes).

Hope this helps.

UPDATE: Read this article about using EARLIER in DAX queries. It might be helpful.

这篇关于在 excel (DAX) 中查找相对于当前行值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！