如何过滤 PowerBI 中所有设置过滤器的集中? [英] How do I filter for the concentrate of all set filters in PowerBI?

问题描述

我有这种形式的数据:

在 PowerBI 中，我添加了另一列，其中仅保留由单个代码组成的代码组合.我用 IF(LEN(Code > 1), "", Code) 之类的东西做到了这一点.结果:

In PowerBI, I added another column where I keep only code combinations that consist of one single code. I did this with something like IF(LEN(Code > 1), "", Code). Result:

这使我能够创建仅包含单个代码的切片器.我还添加了一个显示代码的表格.

This enables me to create a slicer that contains only single codes. I also added a table that shows Codes.

现在，当我在切片器中选择代码时，我希望表格显示这些代码以及它的确切组合.例如，当我选择 A 和 B 时，表格应该显示 A 和 B 以及 A、B.我不希望它显示 A、B、C，尽管它包含 A 和 B.但是，如果我过滤 A、B 和 C，我希望它显示 A、B、C 和 A、B、C - 但不显示 A、B.

Now, when I select codes in the slicer, I want the table to show these codes, plus the exact combination of it. For example, when I select A and B, the table should show me A and B and A, B. I don't want it to show A, B, C although it contains A and B. If I filter for A and B and C, however, I want it to show A and B and C and A, B, C - but not A, B.

我怎样才能做到这一点?

How can I achieve that?

Codes 中的所有条目都保存为字符串.

All entries in Codes are saved as strings.

推荐答案

您的切片器需要一个断开连接(未连接到基表)的表.现在，如果我认为您的基表名称是 - your_table_name，您可以使用以下代码创建新的切片器表 -

You need a disconnected (not connected to the base table) table for your slicer. Now, if I consider your base table name is - your_table_name, you can create the new slicer table with this below code-

slicer = 
SELECTCOLUMNS(
    FILTER(
        your_table_name_8,
        LEN(your_table_name_8[codes]) = 1
    ),
    "code",your_table_name_8[codes]
)

在创建新的 slicer 表后，检查模型是否在 2 个表之间检测到任何自动关系.如果您发现任何关系，只需删除关系.

After creating the new slicer table, check in the model is there any auto relation detected between 2 tables or not. If you find any relation, just Remove the relation.

现在，从新创建的表创建切片器，并在基表中创建以下度量your_table_name-

Now, create your slicer from the newly created table and create this below measure in your base table your_table_name-

show_or_hide = 

VAR current_code = MIN(your_table_name_8[codes])

VAR comma_separated_list = 
CALCULATE (
    CONCATENATEX (
        VALUES(slicer[code]),
        slicer[code],
        ","
    )
)

RETURN 
IF(
    current_code = comma_separated_list || (LEN(current_code) = 1 && CONTAINSSTRING(comma_separated_list,current_code)),
    1,
    0
)

让我们看看结果-

最后，您可以使用新度量 show_or_hide 应用视觉级别过滤器并应用过滤器，以便 True 的值仅显示在视觉效果.

Finally, you can apply a visual level filter using the new measure show_or_hide and apply a filter so that value with True only shown in the visual.

让我们有下表-

现在来自高级查询编辑器的以下代码将提供预期的输出-

Now this following code from Advanced Query Editor will give the the expected output-

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRx0nFWitUBsZx1nJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Column1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Index"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "column_name", each "Column-" & [Value]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"column_name", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Attribute"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Index", "column_name", "Value"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns1",{{"Index", Order.Ascending}, {"column_name", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[column_name]), "column_name", "Value", List.Min),
    #"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"Column-A", "Column-B", "Column-C"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

请一一检查步骤中应用的内容，以便更好地理解.以下是组合中升序的输出-

Please check what applied in steps one by one for better understanding. Here is the output with ascending order in the combination-

添加索引列以从头到尾跟踪行.如果您已经有类似的列，也可以使用该列.

Index column added for keep tracking the row from start to end. If you have already a similar column, you can use that column as well.

这篇关于如何过滤 PowerBI 中所有设置过滤器的集中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！