Analysis Services - 计算成员 - 按维度键范围计数

Hello I´m struggling with MS SSAS... simplified I have two tables in my Data-view. T1 holds a PK and a Date. T2 holds a PK, a FK of T1, and some numbers.

您好我正在努力使用MS SSAS ...简化我的数据视图中有两个表。 T1持有PK和日期。 T2持有PK，T1的FK和一些数字。

I created an dimension from T1 with its two attributes. So I can created a Cube to aggregate the Data of T2 by date and id of T1. So far so good.

我用T1创建了一个带有两个属性的维度。所以我可以创建一个Cube来按日期和T1的ID聚合T2的数据。到现在为止还挺好。

What I want to do next is the aggregation of just a part of that data. Example: "Show me the aggregated data of T2 records that have a FK >=970"

我接下来要做的是只汇总一部分数据。示例：“显示FK> = 970的T2记录的聚合数据”

So I tried to write a calculated measure, but failed. I searched around the web but I found nothing worked so far...

所以我试着编写一个计算出的度量，但失败了。我在网上搜索但是我发现到目前为止没有任何工作......

I hope someone can help with a hint.

我希望有人可以提示一下。

1 个解决方案

#1

If i understood correctly, your date Dimension is T1.

如果我理解正确，你的日期维度是T1。

In your dimension you should have a dimension Key setup, i'll assume you did it at the PK column. Within the properties you can define 3 things for that attribute. KeyColumns NameColumns ValueColumns

在您的维度中，您应该有一个维度密钥设置，我假设您在PK列中执行了此操作。在属性中，您可以为该属性定义3件事。 KeyColumns NameColumns ValueColumns

Make sure your KeyColumns is the PK Column. And should be a OrderBy property you should make sure is set to "Key"

确保您的KeyColumns是PK列。并且应该是OrderBy属性，您应该确保设置为“Key”

After that if your PKs are sequencial(they really should!) you can make a MDX query like this:

之后，如果你的PK是顺序的（他们真的应该！）你可以做这样的MDX查询：

SELECT
{ [Measures].[YOURMEASURE] } ON COLUMNS,
{ [T1].&[970] : NULL } ON ROWS
FROM [MyCube]

EDIT: Sure. Add to your Calculation in the Cube.

编辑：当然。添加到多维数据集中的计算。

CREATE MEMBER CURRENTCUBE.[Measures].[MyOver970Measure]
AS
AGGREGATE({ [T1].&[970] : NULL },[Measures].[YOURMEASURE])
,VISIBLE=1;

#1