表格模型 - 包括空维度成员 [英] Tabular model - include empty dimension members
问题描述
大家好
SSAS表格中的新手。
Fairly new to SSAS Tabular.
为简单起见，我有一个带销售的事实表，以及2个维度：区域和制造商以及量度金额（金额）
For simplicity I have a fact table with sales, and 2 dimensions: region and Manufacturer and a measure sum(amount)
对于我拥有美洲，欧洲，亚洲和非洲的地区。
For regions I have Americas, Europe, Asia and Africa.
制造商是福特，丰田，宝马和通用汽车。假设不是每个地区都出售每辆车，例如我在非洲没有销售，在亚洲我只卖丰田。
Manufacturers are Ford, Toyota, BMW and GM. Assume not every region sells every car, for instance I have no sales in Africa, in Asia I only sell Toyotas.
然而，当我在Excel中导入模型时，我想看到所有区域，无论该区域的事实表中是否有任何行。
However, when I import the model in Excel, I want to see all regions regardless if there is any row in the fact table for that region.
但我希望制造商维度成员仅在实际销售时显示。
But I want the Manufacturer dimension members to only show when there are actual sales.
例如，对于亚洲，它应该显示只有一排（丰田），非洲一排空白制造商和零金额，而美洲可能有两排一个通用汽车和一个福特。
For instance, for Asia it should show only one row (for Toyota), and for Africa one row with a blank manufacturer and a zero amount, while for Americas maybe 2 rows one for GM and one Ford.
另外，如果我不要在我的数据透视表中包含该区域，只应显示事实表中存在的行。
Also, if I don't include the region in my pivot table, only rows that exist in the fact table should be displayed.
为了使其更通用，我希望我的模型/度量设计为对于某些维度，显示所有成员，无论它们是否为空，而其他人总是提出空白
To make this more generic, I want my model/measure to be designed so that for certain dimensions to show all members regardless if they are empty or not, while for others always filet out blanks
我想我可以在事实表中模拟我的维度的虚拟行需要，但这听起来很复杂，我想知道是否有更好的方法
I guess I can simulate dummy rows in the fact table for the dimensions that I need, but this sounds complicated and I was wondering if there is a better approach
谢谢
推荐答案
您可以使用IF（ISBLANK（...强制度量返回）为度量计算添加条件零。
You can add a conditional to your measure calculation using IF(ISBLANK(… to force the measure to return zero.
https://www.decisivedata.net/blog/how-to-display-items-with-no-data-in-a-tabular-model
https://www.decisivedata.net/blog/how-to-display-items-with-no-data-in-a-tabular-model
这篇关于表格模型 - 包括空维度成员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
