DAX 根据度量列计数? [英] DAX to count based on a measure column?
问题描述
我在这里得到了很好的帮助:Power BI、DAX、多对一和关系表格
I was kindly helped here: Power BI, DAX, Many-to-one and relational tables
根据此数据生成度量列:
to produce a measure column based on this data:
Builds =
DATATABLE(
"Build", STRING,
"App", STRING,
{
{ "Build1", "App1" },
{ "Build1", "AppNotInApps1" },
{ "Build1", "App2" },
{ "Build1", "App9" },
{ "Build2", "App3" },
{ "Build2", "AppNotInApps2" },
{ "Build3", "App1" },
{ "Build3", "App5" },
{ "Build3", "App8" },
{ "Build3", "App9" },
{ "Build3", "AppNotInApps3" }
}
)
Apps =
DATATABLE(
"App", STRING,
"Status", STRING,
{
{ "App1", "UAT" },
{ "App2", "Complete" },
{ "App9", "New" },
{ "App3", "Complete" },
{ "App5", "UAT" },
{ "App8", "Complete" }
}
)
许多 Builds 都有一个应用程序,在 Builds.App = Apps.App 上加入.
Many Builds have one Apps, joined on Builds.App = Apps.App.
度量列(称为不完整")就是这个小美女:
The measure column (called 'incomplete') was this little beauty:
incomplete =
IF(
HASONEVALUE( Builds[Build] ),
VAR CurrentBuild =
SELECTEDVALUE( Builds[Build] )
VAR CurrentApp =
SELECTEDVALUE( Apps[App] )
VAR Result =
COUNTROWS(
FILTER(
ALLNOBLANKROW( Builds ),
Builds[Build] = CurrentBuild
&& RELATED( Apps[Status] ) <> "Complete"
&& NOT ISBLANK( RELATED( Apps[Status] ) )
)
) + 0
RETURN
IF( NOT ISBLANK( SELECTEDVALUE( Apps[Status] ) ), Result )
)
自从有了这个解决方案,我还加入了另一个名为 Sites 的表:
Since this solution, I have also joined another table called Sites:
Sites =
DATATABLE(
"Site", STRING,
"Build", STRING,
{
{ "Site1", "Build1" },
{ "Site2", "Build1" },
{ "Site2", "Build3" }
}
)
连接在 Sites.Build = Builds.Build 上.而且您会注意到一个版本可以出现在多个站点中(不要问!).
The join is on Sites.Build = Builds.Build. And you'll notice that one build can appear in more than one site (don't ask!).
我在 Sites.Site 列上有一个切片器.
I have a slicer on the Sites.Site column.
这是一些示例数据:
Site.....Build.....App.....Status.....Incomplete
Site1....Build1....App1....UAT........2
Site1....Build1....App2....Complete...2
Site1....Build1....App9....New........2
Site2....Build1....App2....Complete...0
Site2....Build3....App1....UAT........3
Site2....Build3....App5....UAT........3
Site2....Build3....App8....Complete...1
Site2....Build3....App9....New........3
未完成"让我计算了有多少构建的状态不是完成"
The 'Incomplete' gives me a count of how many Builds have a status that is not 'Complete'
我现在想做的(这就是问题)是在我的 BI 视图中拥有三张卡片.
What i want to do now (and this is the question) is have three cards in my BI view.
卡片一(衡量标准?) - 计算不完整"计数为 0 的 Builds.Build 的 DISTINCT 数量.根据上面的数据,该值为 1.如果我在 Site1 上过滤,这将是 0.
Card one (a measure?) - Count the DISTINCT number of Builds.Build that has an 'incomplete' count of 0. Based on the data above, this value would be 1. If i filtered this on Site1, this would be 0.
卡片二(衡量标准?) - 计算不完整"计数不等于 0 的 Builds.Build 的 DISTINCT 数量.根据上述数据,该值将是2(两个不同的构建或 Build1 和 Build2).如果我在 Site2 上过滤这个,这将是 1 (Build3).
Card two (a measure?) - Count the DISTINCT number of Builds.Build that has an 'incomplete' count that is NOT equal to 0. Based on the data above, this value would be 2 (two distinct builds or Build1 and Build2). If i filtered this on Site2, this would be 1 (Build3).
卡三 - 卡一衡量标准(卡一衡量加上卡二衡量)的百分比
Card three - card one measure as a percentage of (card one measure plus card 2 measure)
我的页面上有过滤器(切片器),所以我需要动态度量.
I have filters (slicers) on my page so i need the measure to be dynamic.
我已经为卡 1 尝试过这个,但它没有给我正确的值?
I've tried this for card 1, but it doesn't give me the correct value?
comp = CALCULATE(
DISTINCTCOUNT(Builds[Build]),
FILTER(ALLSELECTED(Build), [incomplete] = 0))
当然还有卡片 2:
comp = CALCULATE(
DISTINCTCOUNT(Builds[Build]),
FILTER(ALLSELECTED(Build), [incomplete] <> 0))
在我试用我的卡 3 之前,我有什么遗漏吗?
Is there something I'm missing before i try my card 3?
推荐答案
以下 DAX 有效:
COUNTROWS ( FILTER ( VALUES (Builds[build] ), Builds[Incomplete] = 0 ) )
我不完全确定为什么这不起作用:
I'm not entirely sure why this did NOT work:
COUNTROWS( DISTINCT( FILTER(Builds, Builds[Incomplete] = 0 ) ) )
这篇关于DAX 根据度量列计数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!