我在这里得到了帮助: 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" } } )许多Build都有一个Apps,并已加入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的Build.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的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基于度量列进行计数?
发布评论