PowerBI:如何获取表中列的不同计数，同时分别对许多列进行分组 [英] PowerBI: How to get distinct count for a column in a table, while grouping for many columns separately

问题描述

我有一个包含多个日期列和一个标签列的表格，如下代码所示

I have a table with multiple date columns, and a single label column, as shown by following code

Data = DATATABLE (
"Date1", DATETIME,
"Date2", DATETIME,
"Label", STRING,
{
    { "2020-01-01","2020-01-02", "A" },
    { "2020-01-01","2020-01-01", "A" },
    { "2020-01-01","2020-01-02", "B" },
    { "2020-01-01","2020-01-01", "D" },
    { "2020-01-01","2020-01-02", "E" },
    { "2020-01-02","2020-01-01", "A" },
    { "2020-01-02","2020-01-02", "B" },
    { "2020-01-02","2020-01-01", "C" }
}
)

在考虑 date1 以及考虑 date2 时，我想绘制一张每天不同标签计数的图表.这些需要在同一个图中，作为一个聚集的条形图，如下所示.这意味着我需要获取新日期列上的值.

I want to plot a chart of count of distinct labels for each day, when considering date1, as well as when considering date2. These need to be in same plot, as a clustered bar plot, as shown below. This means I need to get the values on a new date column.

预期的结果是这样的，

Date                | value1    | value2
---------------------------------
1/1/2020 12:00:00 AM |  4       |   3 |
1/2/2020 12:00:00 AM |  3       |   3 |

1. 我正在为每个计数创建两个不同的表，如下所示

1. I am creating two different tables for each of the counts, as follows

 Date1_Count = 
 ADDCOLUMNS (
     ALL ( Data[Date1] ),
     "Count",
         CALCULATE (
             DISTINCTCOUNT ( Data[Label] )
         )
 )

和

 Date2_Count = 
 ADDCOLUMNS (
     ALL ( Data[Date2] ),
     "Count",
         CALCULATE (
             DISTINCTCOUNT ( Data[Label] )
         )
 )

然后我创建第三个带有日期的表格，

Then I create a third table with dates as such,

 Final_Counts = CALENDAR("2020-01-01", "2020-01-04")

接下来，我添加三个日期之间的关系，即.Date1_Count 表、Date2_Count 表和 Final_Counts 表

Next, I add relationship between the three dates, viz. Date1_Count table, Date2_Count table, and Final_Counts table

最后，我使用RELATED函数合并数据如下

Finally, I combine the data using RELATED function as follows

 value1 = RELATED(Date1_Count[Count])
 value2 = RELATED(Date2_Count[Count])

问题

是否有不需要为每个日期列创建一个表的更简单的解决方案?当前方法无法扩展到许多日期列.

Question

Is there a simpler solution that does not require creating one table per date column? The current method is not scalable to many date columns.

推荐答案

假设您只有少数日期列，您只需要一个日期维度表和每个日期列一个度量.

Assuming you only have a handful of date columns, you just need a single date dimension table and one measure per date column.

定义要在 x 轴上使用的日期表(与其他表没有关系):

Define a date table to use on the x-axis (no relationships to other tables):

DimDate = CALENDAR("2020-01-01", "2020-01-04")

然后定义将各种日期列与日期表相匹配的度量:

Then define measures that match the various date columns to the date table:

value1 =
CALCULATE (
    DISTINCTCOUNT ( Data[Label] ),
    Data[Date1] IN VALUES ( DimDate[Date] )
)

和

value2 =
CALCULATE (
    DISTINCTCOUNT ( Data[Label] ),
    Data[Date2] IN VALUES ( DimDate[Date] )
)

如果您有多个 DateN 列，那么您最好在取消透视所有这些列的位置重塑数据.

If you have more than a handful of DateN columns, then you'd probably be best served to reshape your data where you unpivot all those columns.

只有这两个你拥有的数据看起来像

For just the two you have the data would look like

在这种情况下，您使用 Unpivot[Column] 作为 Legend 并且只需要一个度量:

In this case, you use Unpivot[Column] as the Legend and only need a single measure:

value = 
CALCULATE (
    DISTINCTCOUNT ( Unpivot[Label] ),
    Unpivot[Date] IN VALUES ( DimDate[Date] )
)

这给出了类似的结果:

这篇关于PowerBI:如何获取表中列的不同计数，同时分别对许多列进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！