英文:
How to calculate max and min values from a comma-separated string column in Power BI?
问题
Max Value = MAX(VALUE(SPLIT([Spalte 2], ",")))
Min Value = MIN(VALUE(SPLIT([Spalte 2], ",")))
英文:
I have a table in Power BI that contains a column with comma-separated values, like this(Spalte 2 actually has 10000 points, i have shown 10 points):
| Spalte 1 | Spalte 2 |
|---|---|
| 1 | 23,45,66,55,11,333,44,55,66,77 |
| 2 | 233,45,66,55,11,333,44,55,66,77 |
| 3 | 23,45,66,55,11,333,44,55,66,773 |
| ... | ... |
I want to add two new columns to the table that show the maximum and minimum values of each row, based on the values in the "Spalte 2" column. For example:
| Spalte 1 | Spalte 2 | Max Value | Min Value |
|---|---|---|---|
| 1 | 23,45,66,55,11,333,44,55,66,77 | 333 | 11 |
| 2 | 233,45,66,55,11,333,44,55,66,77 | 333 | 11 |
| 3 | 23,45,66,55,11,333,44,55,66,773 | 773 | 11 |
| ... | ... | ... | ... |
I tried using the "SPLIT" function, but it did not work. Can someone suggest a DAX formula that can achieve this? Thank you.
Max Value = MAX(VALUE(SPLIT([Spalte 2],",")))
Min Value = MIN(VALUE(SPLIT([Spalte 2],",")))
答案1
得分: 0
在DAX中，你可能可以使用PATH()函数来完成这个任务，但这将是DAX的一种糟糕用法，不是正确的方法。你的数据结构不正确，需要在Power Query中重新组织数据，然后再使用DAX进行分析。
在Power Query中执行以下步骤：
-
起始表格：
-
通过分隔符拆分列并拆分为行：
现在你有两个选择。你可以在Power Query中进行分组：
或者将其不分组，加载到你的模型中，并编写两个度量值，如下所示：
- Max Measure = MAX('Table'[Spalte 2])
- Min Measure = MIN('Table'[Spalte 2])
英文:
You could possibly do this in DAX using PATH() functions but that would be a terrible use of DAX and not the right approach. Your data is in the wrong shape and needs to be reshaped in Power Query before doing analysis in DAX.
Do the following in Power Query:
Starting table:
Split column by delimiter and split to rows:
You now have 2 choices. You can either group by in Power Query:
Or leave it ungrouped, load to your model and write 2 measures as follows:
Max Measure = MAX('Table'[Spalte 2])
Min Measure = MIN('Table'[Spalte 2])
答案2
得分: 0
以下是代码部分的翻译：
```Power Query`中的备选方案，如果您想测试性能。
List.Max( List.Transform (Text.Split([Spalte 2], ","), each Number.From(_)))
请注意，这是您提供的代码的翻译，没有其他内容。
<details>
<summary>英文:</summary>
Here is an alternative in PQ if you want to test performance.
List.Max( List.Transform (Text.Split([Spalte 2], ","), each Number.From(_)))
[![enter image description here][1]][1]
[![enter image description here][2]][2]
[1]: https://i.stack.imgur.com/QwkSy.png
[2]: https://i.stack.imgur.com/Xpm5X.png
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论