DISTINCTCOUNT 并过滤 -1 个 ID
请耐心等待,因为我是 SSAS/MDX 的新手。我希望在事实表的 ID 列上进行 DISTINCTCOUNT 测量,但在过滤 -1 即未知 ID 后。我也喜欢在上面切片/切丁。 这是我的表格
DimStudent
----------------------------
ID Name
-1 Unknown
1 Joe
2 Tim
3 Paul
4 Zina
DimCourse
----------------------------
ID Name
-1 Not Registered
11 Maths
22 Science
33 Arts
FactStudent
---------------
DimStudentID DimCourseID YearID Marks
-----------------------------------------------------------------
1 11 2009 89
1 33 2009 10
1 33 2010 90
1 -1 2009 0
2 -1 2009 0
3 22 2009 90
4 -1 2009 0
在上面的示例中,StudentID 1 有一门 ID 为 -1 的课程,并且他重复了一门课程。如果我对每个学生进行课程不同计数,则学生 ID 1 的值为 3,学生 2 和学生 4 的值为 1。 我的预期结果(不考虑年份)是:
学生姓名课程计数
Joe 2(而不是 3) 蒂姆 0(而不是 1) 保罗 1 Zina 0(而不是 1)
如果只进行不同计数,它应该给我 3 而不是 4。
再次感谢您的帮助,非常感谢。
问候
Please bear with me as I am newbie in SSAS/MDX. I want DISTINCTCOUNT measure on ID column of my fact table but after filtering -1 i.e. unknown IDs. I like to slice/dice on it as well.
Here are my tables
DimStudent
----------------------------
ID Name
-1 Unknown
1 Joe
2 Tim
3 Paul
4 Zina
DimCourse
----------------------------
ID Name
-1 Not Registered
11 Maths
22 Science
33 Arts
FactStudent
---------------
DimStudentID DimCourseID YearID Marks
-----------------------------------------------------------------
1 11 2009 89
1 33 2009 10
1 33 2010 90
1 -1 2009 0
2 -1 2009 0
3 22 2009 90
4 -1 2009 0
In the above example StudentID 1 has one course with -1 ID and he has repeated one course. If I do Course distinct count for each student it will give me 3 for studentid 1 and for student 2 and 4 it will give me 1.
My expected result (without considering year) is:
Student Name Course Count
Joe 2 (instead of 3)
Tim 0 (instead of 1)
Paul 1
Zina 0 (instead of 1)
And if do just distinct count it should give me 3 instead of 4.
Thanks again for your help and it’s highly appreciated.
Regards
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
要从 FactStudent 获取 DimCourseID 的不同计数(不包括 -1 的 DimCourseID 值),请运行从 FactStudent 选择 DimCourseID 的不同计数的查询,其中 DimCourseID <> -1 - 像这样:
To obtain a distinct count of DimCourseID from FactStudent, excluding DimCourseID values of -1, run a query selecting a distinct count of DimCourseID from FactStudent where DimCourseID <> -1 - like so: