如何在分层类别上应用CONTAINS子句以在Power BI中创建方案分析 [英] How to apply CONTAINS clause on hierarchical categories to create scenario analysis in Power BI
问题描述
以@olly()&文件: https://pwrbi.com/so_55281950-2/
在示例文件中,将创建假设或场景分析。使用了两个切片器,一个切片器选择方案,另一个切片器选择对象以应用方案。 @Olly提供了一个聪明的解决方案来处理这种情况:
In the sample file a "what if" or scenario analysis is created. Two slicers are used, one which selects the scenario, and another one which selects the objects to apply the scenario on. @Olly provided a clever solution to deal with the situation:
Value + Trend =
SUMX (
'Demo Fact Table';
'Demo Fact Table'[Value] *
( 1 +
IF (
ISFILTERED ( 'Item Chooser'[Item] ) &&
CONTAINS (
'Item Chooser';
'Item Chooser'[Item];
'Demo Fact Table'[Item]
) &&
HASONEVALUE ( 'Scenario - Trend'[Category] );
VALUES ( 'Scenario - Trend'[Trend Rise] );
'Demo Fact Table'[trend_default]
)
)
)
在此解决方案的基础上,我一直尝试应用相同的方法逻辑,但要解决我的特定问题。在我的问题中,我不仅有一层类别,而且还有3个层次的层次结构。
如果我的类别具有层次结构,我将如何继续应用相同的解决方案?因此,从我的切片器中,我将选择三项内容:Category1,Year和Category2,这将产生选择的Trend_rise。并将其应用到选定的(第4个切片器)行上,即对(项目A,B或/和C)行应用这一Trend_rise
Building upon this solution I've been trying to apply the same logic, but to my specific problem. In my problem I don't only have a single layer of "categories", but instead hierarchy of 3 levels. How would I go on about applying the same solution if my categories had hierarchies? So from my slicer I would select three things: Category1, Year and Category2, which would yield the trend_rise for the selection. and would apply this apply this trend_rise on the selected (4th slicer) rows, i.e. (item A,B or/and C)
category1 - Year - Category2 - trend rise
POSITIVE-trends 2018 low 5%
POSITIVE-trends 2018 high 5%
POSITIVE-trends 2017 low 5%
NEGATIVE-trends 2017 very high -5%
NEUTRAL-trends 2018 low 0%
POSITIVE-trends 2018 high 5%
NEUTRAL-trends 2017 low 5%
NEUTRAL-trends 2016 very high 15%
推荐答案
您只需要对度量进行一点调整即可检查 trend_rise
字段是否具有一个值,如果是,则使用该值,否则使用默认值:
You only need a small tweak to the measure, to check whether the trend_rise
field has one value, and if so then use that, otherwise use the default:
Value + Trend =
SUMX (
'Demo Fact Table',
'Demo Fact Table'[Value] *
( 1 +
IF (
ISFILTERED ( 'Item Chooser'[Item] ) &&
CONTAINS (
'Item Chooser',
'Item Chooser'[Item],
'Demo Fact Table'[Item]
) &&
HASONEVALUE ( 'Scenario - Trend'[Trend Rise] ),
VALUES ( 'Scenario - Trend'[Trend Rise] ),
'Demo Fact Table'[trend_default]
)
)
)
现在您可以在所有列上使用切片器方案表。
Now you can use slicers on all columns of your Scenario table.
请参见 https://pwrbi.com/ so_55332313 / 用于工作示例文件。
See https://pwrbi.com/so_55332313/ for worked example file.
这篇关于如何在分层类别上应用CONTAINS子句以在Power BI中创建方案分析的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!