英文:
Count the number of times a specific value appears in a column when there are more than one value in some of the rows
问题
请问在Power BI中是否有一种度量或DAX，可用于计算某一列中特定值出现的次数，当某些行中有多个值时？
例如：
我不想将表格展开，因为这样会影响表格中的其他数据。
结果
英文:
Please is there a measure or DAX in power bi that can be used to count the number of times a specific value appears in a column when there are more than one value in some of the rows?
For example;
I don' want to unpivot the table because it will mess with the other data in the table.
Result
答案1
得分: 2
假设您已经生成了一个表（名为 Values）包含一个单列（名为 Value）的不同值（例如，“AA”，“AB”，“AC”），创建这个 度量值：
=
VAR ThisValue =
MIN ( 'Values'[Value] )
RETURN
COUNTROWS ( FILTER ( Table1, SEARCH ( ThisValue, Table1[Value],, 0 ) > 0 ) )
并将其添加到可视化中，与来自 Values 表的 Value 字段一起。
英文:
Assuming you've already generated a table (named Values) comprising a single-column (named Value) of distinct values ("AA", "AB", "AC", for example), create this measure:
=
VAR ThisValue =
MIN ( 'Values'[Value] )
RETURN
COUNTROWS ( FILTER ( Table1, SEARCH ( ThisValue, Table1[Value],, 0 ) > 0 ) )
and add it to your visual alongside the Value field from the Values table.
答案2
得分: 2
Sure, here are the translated parts:
步骤1. 创建列和度量用于AA、AB、AC：
列：
AA = 如果(search("AA", [Value],,0) > 0, 1, 0)
度量：
AA度量 = sum('Value'[AA])
与AB、AC相同。
步骤2. 创建新表格：
结果 = datatable(
"Category", STRING,
{{"AA"},{"AB"},{"AC"}})
步骤3. 在新表格中添加列：
值 = switch(true(),
[Category]="AA",[AA度量],
[Category]="AB",[AB度量],
[Category]="AC",[AC度量])
英文:
step1. create column & measure for AA,AB,AC:
COLUMN:
AA = if(search("AA",[Value],,0)>0,1,0)
MEASURE:
AA measure = sum('Value'[AA])
same as AB,AC.
step2. create new table:
Result = datatable(
"Category",STRING,
{{"AA"},{"AB"},{"AC"}})
step3. add column in new table:
Value = switch(true(),
[Category]="AA",[AA measure],
[Category]="AB",[AB measure],
[Category]="AC",[AC measure])
答案3
得分: 0
如果您只想使用Excel公式完成此操作，可以尝试以下步骤。在单元格`C1`中，输入以下公式：
=LET(A, A2:A8,values,DROP(REDUCE("",A,LAMBDA(ac,x,
VSTACK(ac,TEXTSPLIT(x,,"; ",1)))),1), ux, UNIQUE(values), cnts,
BYROW(ux, LAMBDA(x, SUM(N(values=x)))),
VSTACK({"Category","Result"},HSTACK(ux, cnts)))
输出如下：
[![output][1]][1]
有关更多信息，请查看我回答以下问题与`REDUCE/VSTACK`模式相关的问题：https://stackoverflow.com/questions/74477320/how-to-transform-a-table-in-excel-from-vertical-to-horizontal-but-with-different/74497691#74497691。
如果您的数据集不大，连接整个列后不会达到最大单元格大小（`32,767`个字符数），则可以尝试以下操作：
=LET(A, A2:A8, values,TOCOL(TEXTSPLIT(TEXTJOIN(",",,A),"; ",",",1),2),
ux, UNIQUE(values), cnts,BYROW(ux, LAMBDA(x, SUM(N(values=x)))),
VSTACK({"Category","Result"}, HSTACK(ux, cnts)))
第二种解决方案比第一种快约`100倍`。
[1]: https://i.stack.imgur.com/qLnau.png
英文:
In case you want to do it using Excel formulas only, you can try the following. Put in cell C1, the following formula:
=LET(A, A2:A8,values,DROP(REDUCE("",A,LAMBDA(ac,x,
VSTACK(ac,TEXTSPLIT(x,,"; ",1)))),1), ux, UNIQUE(values), cnts,
BYROW(ux, LAMBDA(x, SUM(N(values=x)))),
VSTACK({"Category","Result"},HSTACK(ux, cnts)))
Here is the output:
For more information check my answer to the following question related to the REDUCE/VSTACK pattern: https://stackoverflow.com/questions/74477320/how-to-transform-a-table-in-excel-from-vertical-to-horizontal-but-with-different/74497691#74497691.
If you don't have a large dataset that after joining the entire column won't reach the max cell size (32,767 number of characters), then you can try the following:
=LET(A, A2:A8, values,TOCOL(TEXTSPLIT(TEXTJOIN(",",,A),"; ",",",1),2),
ux, UNIQUE(values), cnts,BYROW(ux, LAMBDA(x, SUM(N(values=x)))),
VSTACK({"Category","Result"}, HSTACK(ux, cnts)))
The second solution is about 100x faster than the first one.
答案4
得分: 0
- 在Power Query中按分隔符分割列；
- 对列进行解除旋转；
- 在列中修剪文本；
- 按值分组并计算行数。
英文:
Do following in Power Query
- First split column by delimiter ;
- Unpivot the columns
- Trim text in column
- Group By value and count rows
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论