英文:
Power BI | DAX | How do I write a DAX formula that returns every category with a count of 1?
问题
我现在的用例是在Power BI的每个“原因”上创建一个视觉化，只有一个计数。
我可以在Power BI桌面的“报表”页面（类似于Excel中的数据透视表）的表格视觉化内创建一个表格，但我希望在Power BI的“数据”页面中以不同的表格或度量方式创建相同的视图。
该过程将是两步的：1）执行“计数”函数以获得“原因”，然后2）将其过滤，只保留每个“原因”计数为1的情况。
| 原因 | 文件编号 |
|---|---|
| 原因 1 | X123 |
| 原因 2 | X124 |
| 原因 3 | X125 |
| 原因 4 | X126 |
| 原因 5 | X127 |
| 原因 6 | X128 |
| 原因 7 | X129 |
| 原因 8 | X130 |
| 原因 9 | X131 |
| 原因 10 | X132 |
我尝试使用Power BI中的“卡片”视觉化，并包括“原因”，并将其转换为计数。但是，当我转到“此视觉化上的筛选器”并尝试操纵筛选器以“筛选类型”=前N项时，没有选项可以将其更改为“IS = 1”。
我还尝试转到数据页面并使用“SUMMARIZE”函数返回不同的原因，但它不会在旁边返回“计数”列。
英文:
The use case I have is a Power BI visual for every "reason" that only has a count of 1.
I can create a table within the table visual in the 'Report' page of Power BI desktop (similar to a pivot table in excel), but I want to create this same view within the 'Data' page in Power BI in a different Table or as a measure.
The process would be two-fold, 1) Perform a 'count' function for the "Reasons", and then 2) Filter it to only have every "Reasons" with a count of 1.
| Reasons | File Number |
|---|---|
| Reason 1 | X123 |
| Reason 2 | X124 |
| Reason 3 | X125 |
| Reason 4 | X126 |
| Reason 5 | X127 |
| Reason 6 | X128 |
| Reason 7 | X129 |
| Reason 8 | X130 |
| Reason 9 | X131 |
| Reason 10 | X132 |
| Reason 1 | X133 |
| Reason 1 | X134 |
| Reason 1 | X135 |
| Reason 1 | X136 |
| Reason 1 | X137 |
| Reason 1 | X138 |
| Reason 3 | X139 |
| Reason 3 | X140 |
| Reason 3 | X141 |
| Reason 3 | X142 |
| Reason 3 | X143 |
| Reason 3 | X144 |
| Reason 3 | X145 |
| Reason 3 | X146 |
| Reason 6 | X147 |
| Reason 6 | X148 |
| Reason 6 | X149 |
| Reason 6 | X150 |
| Reason 6 | X151 |
| Reason 6 | X152 |
| Reason 6 | X153 |
| Reason 6 | X154 |
| Reason 9 | X155 |
| Reason 9 | X156 |
| Reason 9 | X157 |
| Reason 9 | X158 |
| Reason 9 | X159 |
| Reason 9 | X160 |
| Reason 9 | X161 |
| Reason 9 | X162 |
| Reason 9 | X163 |
| Reason 10 | X164 |
| Reason 10 | X165 |
| Reason 10 | X166 |
| Reason 10 | X167 |
| Reason 10 | X168 |
| Reason 10 | X169 |
| Reason 10 | X170 |
| Reason 10 | X171 |
I tried using a 'Card' visual in Power BI and included the Reasons and turned it into a count. But, when I go to the 'Filters on this Visual' and try to manipulate the filtering to "Filter Type" = Top N, it is not an option to change it to 'IS = 1'.
I also tried going to the Data page and using the 'SUMMARIZE' function to return the distinct reasons, but it does not return a 'count' in a column next to it.
答案1
得分: 1
添加一个新的计算表如下：
表2 =
ADDCOLUMNS(
SUMMARIZE('表', '表'[原因]),
"@Count", CALCULATE(COUNT('表'[文件编号]))
)
或者
表2 =
FILTER(
ADDCOLUMNS(
SUMMARIZE('表', '表'[原因]),
"@Count", CALCULATE(COUNT('表'[文件编号]))
),
[@Count] = 1
)
英文:
Add a new calculated table as follows:
Table 2 =
ADDCOLUMNS(
SUMMARIZE('Table', 'Table'[Reasons]),
"@Count", CALCULATE(COUNT('Table'[File Number]))
)
Or
Table 2 =
FILTER(
ADDCOLUMNS(
SUMMARIZE('Table', 'Table'[Reasons]),
"@Count", CALCULATE(COUNT('Table'[File Number]))
),
[@Count] = 1
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论