DAX：如何编写IF语句以返回针对多个（特定）选定值的计算？ [英] DAX: How do I write an IF statement to return a calculation for multiple (specific) values selected?
问题描述
这真让我发疯。假设我们要使用具有两个不同值的切片器，以便从维度中进行选择。还有A和B。
This is driving me nuts. Let's say we want to use a slicer which has two distinct values to choose from a dimension. There is A and B.
让我们也说一下，事实表已与此维度相关联，但是它具有相同的维度，具有更多选项。
Let us also say that my Fact table is connected to this dimension, however it has the same dimension with more options.
我的切片器现在有A，B和（空白）。没什么大不了的。
My slicer now has A, B and (Blank). No biggie.
现在让我说，我想通过在DAX公式中选择切片器来列出所有可能的计算结果，但是在我的视觉中，我需要将所有这些结果都列在一个 IF（）分支公式：
Let's now say I want to list out all of the possible calculation outcomes by selecting the slicer in a DAX formula, but in my visual I need all those outcomes to be listed in an IF() branched formula:
我可以列出A：
IF(MAX(SlicerDim[Column]) = "A", CALCULATE([Calculation], SlicerDim[Column] = "A")
我可以列出B：
IF(MAX(SlicerDim[Column]) = "A", CALCULATE([Calculation], SlicerDim[Column] = "A")
我也可以列出（空白）计算：
I can list out the (Blank) calculation too:
CALCULATE([Calculation], SlicerDim[Column] = Blank())
即使在所有切片器元素处于打开或关闭状态时，我也设法使用以下方法进行计算：
And I've managed to get a calculation out of it even when all of the slicer elements are on or off, using:
NOT(ISFILTERED(SlicerDim[Column])), CALCULATE([Calculation], SlicerDim[Column] = "A" || SlicerDim[Column] = "B")
注意，我需要这个 IF（）分支才能实际使用A&返回值。 B值，所以现在我有选择A或B或（空白）或All或None的返回值；但当A&的多个值不等于
Notice I need this IF() branch to actually return a calculation using A & B values, so now I have returns for when A or B or (Blank) or All or None are selected; BUT NOT when multiple values of A & B are selected!
我如何写出这个IF（）分支以使其返回相同的值，但是当两个A& B被选中？由于切片器中只有两个 real 选项-我设法使用MIN（）和MAX（）通过使用它们的名称或索引号使其工作。
How do I write out this IF() branch for it to return the same thing, but when both A & B are selected? Since there are only two real options in the slicer - I managed to use MIN() and MAX() get it to work by using their names or Index numbers.
IF((MIN(SlicerDim[Column]) = "A" && MAX(SlicerDim[Column]) = "B") || NOT(ISFILTERED(Paslauga[Paslauga])), CALCULATE([Calculation], SlicerDim[Column] = "A" || SlicerDim[Column] = "B")
但是-我想要一个更易于理解/更健壮/可重用的公式，以便我可以从切片器中列出许多可选值，并让它返回针对特定选择的切片器的计算结果值。
BUT - I want a more understandable/robust/reusable formula, so that I could list out many selectable values from the slicer and have it return a calculation for specifically selected slicer values.
请帮忙
我一直在搜寻高点和低点，尽管刮掉IF路线并仅使用
Please, help. I've been searching high and low and there seems to not be an easy way to fix this albeit scraping the IF route and just using a damn slicer for this type of dilemma.
TL; DR：
如何使用DAX编写IF（）分支计算以在全/无时获得结果或非空白或特定切片器值ar
我最大的努力：
我希望改进第一个IF（）分支而不必使用MIN / MAX，因为我想如果切片器中有两个以上的实数选项，则能够重用这种类型的公式：
My best effort: I am looking to improve the first IF() branch to not have to use MIN/MAX, because I would like to be able to reuse this type of formula if there were more than two real options in the slicer:
IF_branch =
IF((MIN(SlicerDim[Column]) = "A" && MAX(SlicerDim[Column]) = "B" || NOT(ISFILTERED(SlicerDim[Column])), CALCULATE([Calculation], SlicerDim[Column] = "A" || SlicerDim[Column] = "B"),
IF(MAX(SlicerDim[Column]) = "A", CALCULATE([Calculation], SlicerDim[Column] = "A"),
IF(MAX(SlicerDim[Column]) = "B", CALCULATE([Calculation], SlicerDim[Column] = "B"),
CALCULATE([Calculation], SlicerDim[Column] = BLANK()))))
推荐答案
Think what you are looking for is CONTAINS and VALUES
VALUES将为您提供当前范围内的独特选择。
CONTAINS可让您检查表是否包含具有一组值的任何行。
VALUES will give you the distinct current selection in scope. CONTAINS lets you check if a table contains any row with a set of values.
[]
公式：
selected Scenarios = CONCATENATEX(VALUES(DimScenario[ScenarioName]);[ScenarioName];";")
Contains Forecast and Budget? =
IF(
CONTAINS(VALUES(DimScenario[ScenarioName]);[ScenarioName];"Forecast") &&
CONTAINS(VALUES(DimScenario[ScenarioName]);[ScenarioName];"Budget")
;"Yes"
;"No"
)
这篇关于DAX：如何编写IF语句以返回针对多个（特定）选定值的计算？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
