问题

I have this DAX formula and cannot seem to get it to work for the first condition. When person 1 and person 2 are filtered it should return a total sum for those two people. Instead - 0 is returned. How is my logic flawed here?

sum_filter = 
VAR selected = VALUES(table1[people])
VAR excluded = {"person1","person2"}
IF(
   ISEMPTY(EXCEPT(selected, excluded)),
   "TGT: $" & FORMAT(SUM(table1[value]),"#,##0"),
   "TGT: $" & FORMAT(SUM(table1[value]) - CALCULATE (SUM(table1[value]), table1[people] IN excluded),"#,##0")
)

Example output:
If person 1 and 2 are filtered (I want these to sum when they are filtered)

people    value
person1   1
person2   2
total     3

If person 1,2, and now 3 are filtered - only person 3 gets summed

people    value
person1   1
person2   2
person3   5
total     5

In my real scenario I need to sum if any of persons 1-5 are filtered. So if person 1 is filtered it sums, if persons 2-3 are filtered it sums them, if all persons 1-5 are filtered it sums them - but as soon someone outside of persons 1-5 are added to the filter - it excludes persons 1-5 and you would only get the the sum of persons 6+

In the above - I see the values for person 1 and person 2 in tabular form when using the table visual. But total returned in totals row is 0. This would be the same if I was condensing the value to a single card.

When I filter other persons outside of 1 and 2, it returns the correct value of any other persons minus person 1 and 2 values. But again, with just person 1 and 2 filtered - the value returned is 0 when they have values to sum. It looks like SUM(SELECTEDVALUE(table1[value])) is failing. What can I do here, thank you!

sum_filter = 
VAR selected = VALUES(table1[people])
VAR excluded = {"person1","person2"}
IF(
   ISEMPTY(EXCEPT(selected, excluded)),
   "TGT: $" & FORMAT(SUM(table1[value]),"#,##0"),
   "TGT: $" & FORMAT(SUM(table1[value]) - CALCULATE (SUM(table1[value]), table1[people] IN excluded),"#,##0")
)

people    value
person1   1
person2   2
total     3

people    value
person1   1
person2   2
person3   5
total     5

答案1

得分: 2

以下是您要的翻译部分：

首先，有一些问题出现在这里。首先，您应该了解筛选上下文，这是DAX中最基本的概念之一，您对它的理解不足是导致问题的一部分。

此外，您应该阅读SELECTEDVALUE函数的文档（https://learn.microsoft.com/en-us/dax/selectedvalue-function），在那里您会发现它*“在columnName的上下文已经筛选到一个唯一值时返回该值。否则返回alternateResult。”*

在您的示例中，当您位于表视觉中的个别行时，table1[people]的筛选上下文只有一个单一值，因此SELECTEDVALUE '起作用'并返回该值，并将其与您的列表进行比较 - 如果该值是person 1或person 2，则进入IF语句的“true”路径并计算该人的值之和，否则进入“false”路径。

然而，在总行上（您至少选择了2个人的地方），您并没有"筛选到一个唯一值"，而是筛选了所有您当前筛选到的值。因此，SELECTEDVALUE(table1[people])返回一个空值，显然不在您的列表中，因此您的IF语句进入“false”路径。

这里涉及到 SUM(table1[value]) - SUMX(FILTER(table1, [people] IN {"person1","person2"}), table1[value]) ，这是筛选上下文真正发挥作用的地方。当table1[people]的筛选上下文是person 1和person 2时，

SUM(table1[value]) 和
SUMX(FILTER(table1, [people] IN {"person1","person2"}), table1[value])
都返回相同的值，因此从一个数中减去它本身会使您得到零，这就是您在总行上看到的结果。

我建议您使用类似以下的方式：

VAR Selected = VALUES(table1[people])  //筛选上下文中人员值的列表
VAR Excluded = {"person 1", "person 2"} 
RETURN 
IF(
    //检查所选值和'excluded'列表是否匹配
    ISEMPTY (EXCEPT (Selected,Excluded)), 
    //如果匹配，则对所选值求和
    SUM(table1[value]),
    //否则，对所选值求和并减去被排除值的求和
    SUM(table1[value]) - CALCULATE (SUM(table1[value]),table1[people] IN Excluded) 
)```

<details>
<summary>英文:</summary>

So there are a couple of things going wrong here. First off, you should learn about filter context, which is one of the most fundamental concepts in DAX, and your lack of understanding of it is part of what&#39;s causing you problems.

Additionally, you should read the documentation for SELECTEDVALUE (https://learn.microsoft.com/en-us/dax/selectedvalue-function) where you&#39;ll find that it *“Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.”*

In your example, when you&#39;re on the individual rows in the table visual your filter context has a single value for table1[people], so SELECTEDVALUE &#39;works&#39; and returns that value and compares it to your list - if the value is person 1 or person 2 it goes to the &#39;true&#39; path of your IF statement and calculates the sum of that persons value, otherwise it goes to the &#39;false&#39; path. 

However on the total rows (where you&#39;ve got at least 2 people selected) you&#39;re not &quot;*filtered down to one distinct value only*&quot; but rather you&#39;ve got all the values you&#39;re currently filtered to. So SELECTEDVALUE(table1[people]) returns a blank, which obviously isn&#39;t in your list, so your IF statement goes to the false path.

Here it gets to `SUM(table1[value]) - SUMX(FILTER(table1, [people] IN {&quot;person1&quot;,&quot;person2&quot;}), table1[value])` and this is where filter context really kicks in. When your filter context on table1[people] is person 1 and person 2 then 

`SUM(table1[value])` and 
`SUMX(FILTER(table1, [people] IN {&quot;person1&quot;,&quot;person2&quot;}), table1[value])` both return the same value, so subtracting a number from itself leaves you with zero, which is what you&#39;re seeing on the total row.

I would suggest you use something like

    sum_filter = 
    VAR Selected = VALUES(table1[people])  //List of the people values in the filter context 
    VAR Excluded = {&quot;person 1&quot;, &quot;person 2&quot;} 
    RETURN 
    IF(
        //checks if the selected values and the &#39;excluded&#39; list match
        ISEMPTY (EXCEPT (Selected,Excluded)), 
        //if so, do the sum of the selected values
        SUM(table1[value]),
        //otherwise take the sum of the selected values and subtract the sum of hte excluded values
        SUM(table1[value]) - CALCULATE (SUM(table1[value]),table1[people] IN Excluded) 
    )

</details>



# 答案2
**得分**: 0

sum_filter = 
IF(
   ISFILTERED(table1[people]) &amp;&amp; FIRSTNONBLANK(table1[people], 1) IN {&quot;person1&quot;,&quot;person2&quot;},
   &quot;MTD: &quot; &amp; FORMAT(SUM(table1[value]),&quot;#,##0&quot;),
   &quot;MTD: &quot; &amp; FORMAT(SUM(table1[value]) - SUMX(FILTER(table1, [person] IN {&quot;person1&quot;,&quot;person2&quot;}),tabl1[value2]),&quot;#,##0&quot;)
)

<details>
<summary>英文:</summary>

I was able to solve with:

sum_filter =
IF(
ISFILTERED(table1[people]) && FIRSTNONBLANK(table1[people], 1) IN {"person1","person2"},
"MTD: " & FORMAT(SUM(table1[value]),"#,##0"),
"MTD: " & FORMAT(SUM(table1[value]) - SUMX(FILTER(table1, [person] IN {"person1","person2"}),tabl1[value2]),"#,##0")
)

It seems that `FIRSTNONBLANK()` makes the summing certain people when filtered and excluding certain people when group 1 filter people are mixed with group 2 filter people. I.e., summing happens with all of group 1 people are filtered, summing on only group 1 people happens only to that group when all of group 1 and 2 are filtered, and when anyone in group 2 is filtered with group 1 unfiltered, then group 2 is summed. 

It looks like using `FIRSTNONBLANK()` helps isolate a single value of the list as the first detected value of the list. It also looks like it can break if the first non blank is in fact the first item in the list to sum all if condition 1 is met. If that is true, condition 1 triggers regardless.

</details>