如何在一个度量中创建两个关联列的不同计数列 [英] How do I create a distinct count column of two associated columns in one measure

问题描述

我有一个看起来像这样的数据集

I have a dataset that looks like this

ID 1   ID 2
123456 234567
123456 234567
234567 123456
345678 123456
345678 234567
345678 456789
123456 345678
234567 123456

我创建了一个度量，如果添加到可视化表中，它看起来像这样

I create a measure that if added to a visual table would look like this

ID 1   Count of unique ID 2
123456 2
234567 1
345678 3

通知123456"；仅与234567"相关联.和345678".234567"仅与123456"相关联因此独特"很重要.

Notice "123456" is associated with only "234567" and "345678". "234567" is only associated with "123456" hence "unique" counts.

我创建了一个看起来像这样的度量.

I created a measure that looks like this.

Unique ID involved = DISTINCTCOUNT(TABLE[ID 2])

但是，这仅在我将度量与表中的第一列(ID 1)相关联时才有效，而不是在实际度量中.当我单独运行它时，它会计算第 2 列(ID2)中的唯一值，而不是我想要的.我想在一个度量或表格中执行相对于 ID1 关联的 ID2 计数".这可能吗?

However this only works when I associate the measure with the first column(ID 1) like in a table but not in the actual measure. When I run it alone it counts the unique values in column 2(ID2) and its not exactly what I want. I want to do in one measure or table the "count of ID2 associated relative to ID1". Is this possible?

另请注意，由于视觉设置了所需的输出，有没有办法将视觉结果作为表格拉出，或者创建一个衡量视觉结果的度量，如果更容易也可以通过过滤器动态调整?

Also note since the visual sets the desired output, is there a way to just pull the result of the visual as a table or create a measure off the visual result that adjusts dynamically through filters as well if easier?

推荐答案

你可以试试这个

Measure = COUNTX(SUMMARIZE('fact','fact'[ID1],'fact'[ID 2]),'fact'[ID 2])

这篇关于如何在一个度量中创建两个关联列的不同计数列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！