英文:
How to combine two rows values into one in power bi using DAX and not in Power Query?
问题
我有一个名为“Business Unit”的表格，如下所示 -
|Business Unit|
|-|
|A.1|
|A.2|
|A.3|
|B|
|C|
我希望行A.1、A.2和A.3的值都变成A。新表格应该如下所示 -
|Business Unit|
|-|
|A|
|B|
|C|
我尝试了以下的DAX表达式，但显然有错误 -
Business Unite Measure =
CALCULATETABLE( 'Events',
SWITCH( 'Events'[Business Unit],
"A.1", "A",
"A.2", "A",
"A.3", "A",
"B", "B",
"C", "C", BLANK() ) )
在Power Query中可以实现所需的表格，但是否有办法使用DAX获得结果？
英文:
I have a table with name "Business Unite" as below -
| Business Unit |
|---|
| A.1 |
| A.2 |
| A.3 |
| B |
| C |
I want the values for the rows A.1, A.2 and A.3 to be as A only.
The new table should look like the below example -
| Business Unit |
|---|
| A |
| B |
| C |
I have tried the below DAX expression but obviously, it has an error -
Business Unite Measure =
CALCULATETABLE( 'Events',
SWITCH( 'Events'[Business Unit],
"A.1", "A",
"A.2", "A",
"A.3", "A",
"B", "B",
"C", "C", BLANK() ) )
The desired table is achievable in Power Query but is there any way to get the result using DAX?
答案1
得分: 1
以下是翻译好的内容：
这里是关于您测量计算的答案：
如果您想要进行测量，公式将如下：
业务单元测量 =
SWITCH( SELECTEDVALUE('Events'[Business Unit]),
"A.1", "A",
"A.2", "A",
"A.3", "A",
"B", "B",
"C", "C",
BLANK() )
但正如Rabbani所提到的，您不能将此度量用作切片器、X轴或Y轴，如果您想在表中显示它，应该与表视觉中的原始[Business Unit]列一起使用。否则，创建的度量将不会显示。
英文:
Here answer for your measure calculation:
If you want measure the formula would be:
Business Unite Measure =
SWITCH( SELECTEDVALUE('Events'[Business Unit]),
"A.1", "A",
"A.2", "A",
"A.3", "A",
"B", "B",
"C", "C",
BLANK() )
But as Rabbani mentioned you cannot use this measure as Slicer or X-axis or Y-axis, if you want to show in table you should use it with original [Business Unit] column in table visual. Otherwise the created measure will not show.
答案2
得分: 0
在表格中创建一个新的列（请注意，应该是列而不是度量）：
Business Unit2 =
SWITCH( [Business Unit],
"A.1", "A",
"A.2", "A",
"A.3", "A",
"B", "B",
"C", "C",
BLANK() )
然后，您可以将这个新列用作X轴，将其他值度量用作Y轴，添加到任何可视化中。
英文:
Create a new column in table (note that should be column not measure):
Business Unit2 =
SWITCH( [Business Unit],
"A.1", "A",
"A.2", "A",
"A.3", "A",
"B", "B",
"C", "C", BLANK() )
Then you can use this new column as X-axis & other value measures as Y-axis to any visuals.
答案3
得分: 0
步骤 1:
为您想要查看的任何值创建值度量：
值度量 = SUM(Events[Value])
步骤 2:
创建新表格：
新表格 = DATATABLE("业务单元2",STRING,
{{"A"},{"B"},{"C"}})
步骤 3:
在新表格中创建新列：
值 - 版本 1 = SWITCH([业务单元2],
"A",CALCULATE([值度量],SEARCH("A",'Events'[业务单元],1,0)>0),
"B",CALCULATE([值度量],SEARCH("B",'Events'[业务单元],1,0)>0),
"C",CALCULATE([值度量],SEARCH("C",'Events'[业务单元],1,0)>0))
值 - 版本 2 = SWITCH([业务单元2],
"A",CALCULATE([值度量],
'Events'[业务单元]="A.1"||
'Events'[业务单元]="A.2"||
'Events'[业务单元]="A.3"),
"B",CALCULATE([值度量],'Events'[业务单元]="B"),
"C",CALCULATE([值度量],'Events'[业务单元]="C"))
结果如下：
英文:
Another option write here as add comment in previous answer cannot make newline may not so clear:
Step 1:
Create value measure for any values you want to see:
Value Measure = SUM(Events[Value])
Step 2:
Create new table:
New Table = DATATABLE("Business Unit2",STRING,
{{"A"},{"B"},{"C"}})
Step 3:
Create new column in new table:
Value - Version 1 = SWITCH([Business Unit2],
"A",CALCULATE([Value Measure],SEARCH("A",'Events'[Business Unit],1,0)>0),
"B",CALCULATE([Value Measure],SEARCH("B",'Events'[Business Unit],1,0)>0),
"C",CALCULATE([Value Measure],SEARCH("C",'Events'[Business Unit],1,0)>0))
Value - Version 2 = SWITCH([Business Unit2],
"A",CALCULATE([Value Measure],
'Events'[Business Unit]="A.1"||
'Events'[Business Unit]="A.2"||
'Events'[Business Unit]="A.3"),
"B",CALCULATE([Value Measure],'Events'[Business Unit]="B"),
"C",CALCULATE([Value Measure],'Events'[Business Unit]="C"))
Results as below:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论