Sql 具有分组条件的不同计数 (DAX)

发布于08月22日

我的DAX查询有问题，希望您能帮助我:)

我想创建一个计入用户Story(在表中标识为迭代路径)已经存在的Sprint数量的度量方法.但是，如果用户情景在任何Sprint中都已关闭(标识为State)，我希望度量返回0.

Title	Iteration Path	State
Subscription order intake	Sprint 10	Active
Subscription order intake	Sprint 10	Closed
Subscription order intake	Sprint 10	New
Source of truth views for list prices and cost prices	Sprint 09	New
Source of truth views for list prices and cost prices	Sprint 10	Active
Source of truth views for list prices and cost prices	Sprint 10	New
Source of truth views for list prices and cost prices	Sprint 11	Active
Source of truth views for list prices and cost prices	Sprint 12	Active
Source of truth views for list prices and cost prices	Sprint 12	Closed
Sales order views	Sprint 06	Active
Sales order views	Sprint 08	Active
Sales order views	Sprint 09	Active
Sales order views	Sprint 10	Active
Sales order views	Sprint 11	Active
Sales order views	Sprint 11	Closed
Quotes with longer than 6 months delivery time.	Sprint 06	New
Project economy - Data model	Sprint 06	Active
PrimeServ assist - Data model	Sprint 06	Active
PrimeServ assist - Data model	Sprint 08	Active
PrimeServ assist - Data model	Sprint 09	Active
Prepare Work shop and kick-off	Sprint 09	New
Prepare Work shop and kick-off	Sprint 10	Active
Prepare Work shop and kick-off	Sprint 10	New
Prepare Work shop and kick-off	Sprint 11	Active
Prepare Work shop and kick-off	Sprint 12	Active

仅作为示例，"订阅订单接收"、"价目表价格和成本价格的真实来源视图"和"销售订单视图"的返回值将为0.但是，"交付时间超过6个月的报价"的返回值将为1，而"PrimeServ辅助数据模型"的返回值将为3.这将产生类似下表的结果:

Title	Measure output
Subscription order intake	0
Source of truth views for list prices and cost prices	0
Sales order views	0
Quotes with longer than 6 months delivery time.	1
Project economy - Data model	1
PrimeServ assist - Data model	3
Prepare Work shop and kick-off	4

我希望我的解释是正确的，提前谢谢！

Measure = VAR t = CALCULATETABLE(VALUES('Table'[Title]), FILTER('Table', 'Table'[State] = "Closed") ) RETURN CALCULATE( DISTINCTCOUNT('Table'[Iteration Path]), FILTER('Table', NOT ('Table'[Title] IN t)) )+0

Sql 具有分组条件的不同计数 (DAX)

推荐答案

Sql相关问答推荐

SUM(条件)在Oracle？

为表中每个缺少的引用创建新行

使用WHERE子句进行筛选时，SQL SELECT查询返回总计数

值对于类型字符来说太长

在SQL Server中设置关联对象的有效JSON格式

无法将发票与产品价格相关联

SQL数据库规范化与数据插入

基于另一个(SAS、SQL)中的值更新列

排除具有部分匹配条件的记录

存储过程太慢

在 SQL Server 中合并两个 XML 列

用替代方案替换 SQL Cursor 以提高性能

使用临时表判断记录是否存在 - 如果存在则执行相同的操作

SQL 根据前一天的最大值计算每天的值数

特殊条件计算小计

SQL 将 Varchar 转换为日期

SQL for Smarties 类型问题：从表中 Select 记录，并对某些值进行分组

如何将多行的查询结果合并为一行

条件意外地显着降低性能的地方

PostgreSQL 如何在一组项目及其数量上找到完全相同的订单？