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] 作为图例，并且只需一个度量即可:

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屋！