将逗号分隔的文本转换为数字列表 [英] Convert comma separated text to a list of numbers

问题描述

在Power BI中，我创建了一个DAX查询，该查询是使用 CONCATENATEX 函数使用var给出逗号分隔的文本创建的。

输出类似

  Var = 1,2,3,4,5,6 
  

现在，我想使用语法将此变量搜索到我的表列中

< {var}中的pre> Table [col]。


正在抛出错误。

我什至尝试用语法将列转换为字符串

 在{var} $ b $中转换（表[col]，字符串） b  

已删除错误，但数据与列数据不匹配。

解决方案

我发现

---

请注意，这是不可能的创建可动态响应报表过滤器和切片器的计算表。物化计算表仅在数据加载时才计算一次，而不是每次您调整过滤器或切片器时都计算一次。

因此，您可以在表中使用度量代替字符串 1,2,3,4,5,6 ，但是无论度量在不同的过滤器上下文中返回什么，表的输出将相同。

In Power BI, I have created a DAX query created with a var giving comma-separated text using CONCATENATEX function.

Output like

Var = "1,2,3,4,5,6"

Now I want to search this var into my table column with syntax

Table[col] in {var}.

It is throwing an error.

I even tried converting the column to string with syntax

Convert(table[col], string)  in {var}

The error is removed but the data doesn't match with column data.

解决方案

I found this Power BI community thread that is essentially the same question.

The solution is to convert the string into a path object.

Here's the code from the link:

Measure 3 =
VAR mymeasure =
    SUBSTITUTE ( [Measure], ",", "|" )
VAR Mylen =
    PATHLENGTH ( mymeasure )
VAR mytable =
    ADDCOLUMNS (
        GENERATESERIES ( 1, mylen ),
        "mylist", VALUE ( PATHITEM ( mymeasure, [Value] ) )
    )
VAR mylist =
    SELECTCOLUMNS ( mytable, "list", [mylist] )
RETURN
    CALCULATE ( COUNTROWS ( Table1 ), Table1[ID] IN mylist )

---

There's a bit of redundancy in the above, so I'd probably condense it a bit and write it like this:

SomeMeasureFilteredByVar =
VAR PathVar = SUBSTITUTE ( [Var], ",", "|" )
VAR VarList =
    SELECTCOLUMNS (
        GENERATESERIES ( 1, PATHLENGTH ( PathVar ) ),
        "Item", VALUE ( PATHITEM ( PathVar, [Value] ) )
    )
RETURN
    CALCULATE ( [SomeMeasure], 'Table'[ID] IN VarList )

---

Edit: To just create a calculated table, you can skip the last step:

TableFromString =
VAR PathVar = SUBSTITUTE ( "1,2,3,4,5,6", ",", "|" )
RETURN
    SELECTCOLUMNS (
        GENERATESERIES ( 1, PATHLENGTH ( PathVar ) ),
        "Item", VALUE ( PATHITEM ( PathVar, [Value] ) )
    )

---

Note that it is not possible to create a calculated table that is dynamically responsive to report filters and slicers. Materialized calculated tables are only calculated once when the data loads, not every time you adjust a filter or slicer.

Therefore, you can use a measure in the table instead of the string "1,2,3,4,5,6" but the table output will be the same regardless of what the measure returns within different filter contexts.

这篇关于将逗号分隔的文本转换为数字列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！