MDX 计算度量 - 开始日期和结束日期之间的计数
我有一个维度“公司”，其中包含成员“CompanyName”和度量值组“CompanyMembership”，其中包含度量值“StartDate”和“EndDate”。如何计算某一日期、某月或某年拥有会员资格的公司数量？我还有一个用户层次结构 Date.Calendar （就像 Adventure Works 中的那样）。
如果当前会员的日期代表一个月，则会员值将是该月的第一天，我只关心会员资格是否涵盖那一天。如果是一年，会员价值将是一年的第一天，我只关心会员资格是否涵盖这一天。
这是我想要的一些伪代码： sum( if (Measures.StartDate <= Date.Calendar.Value <= Measures.EndDate) then 1 else 0)
我想将 StartDate 和 EndDate 保留在同一事实表行中（不分成两行 - 一行带有StartDate 和 EndDate），但如果没有其他方法，愿意更改架构。
我该如何写这个表达式？
I have a dimension "Company" which has a member "CompanyName" and a measure group "CompanyMembership" which has measures "StartDate" and "EndDate". How do I count the number of companies that have membership for a date or month or year? I also have a user hierarchy Date.Calendar (like the one in Adventure Works).
If the date current member represents a month the member value will be the first day of the month and I only care if the membership covers that day. If a year the member value will be the first day of the year and I only care if the membership covers that day.
Here is some pseudocode for what I would like:
sum( if (Measures.StartDate <= Date.Calendar.Value <= Measures.EndDate) then 1 else 0)
I would like to keep StartDate and EndDate in the same fact table row (not split into two rows - one with StartDate and one with EndDate) but would be willing to change the schema if there's no other way.
How can I write this expression?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我没有在您当前的架构中执行此操作的方法，但您确实应该考虑将成员资格设置为时间维度。这就需要回答“这家公司在这一天是会员吗？”容易多了。
I don't have a method for doing this in your current schema, but you should really consider making Membership a time dimension. That would make answering "was this company a member on this date?" much easier.