如何在层次类别上应用 CONTAINS 子句以在 Power BI 中创建场景分析

本文介绍了如何在层次类别上应用 CONTAINS 子句以在 Power BI 中创建场景分析的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

基于@olly 给出的解决方案(Power BI:如何进行场景分析，其中选择器查找"切片器中的选择值并从该行获取值) &文件:pwrbi/so_55281950-2/

Building upon the solution given by @olly (Power BI: How to scenario analysis, where the selector "looks up" the select value from slicer and gets values from that row) & file: pwrbi/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 个切片器)行上应用 thistrend_rise，即(项目 A、B 或/和 C)

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] ) ) )

现在您可以在 Scenario 表的所有列上使用切片器.

Now you can use slicers on all columns of your Scenario table.

有关工作示例文件，请参阅 pwrbi/so_55332313/.

See pwrbi/so_55332313/ for worked example file.