表格模型 - 建模问题 [英] Tabular model - Modelling question

问题描述

大家好

我有一个基于数据的事实表已经预先汇总在各个层面。出于隐私原因，我无法访问原始数据。

衡量指标是人数和工资中位数。中位数工资实际上并不是一个附加指标而且数量都不是（长期解释，是出于隐私原因隐藏在一些非常低的粒度级别的详细数据）。

因此我不能使用任何聚合函数来计算平均值，加权平均值，总和等度量，它们必须以它们的存储方式显示。

我的数据将是所有维度成员的交叉积分加上一个"全部"每个维度组合的行。添加到我的数据中的每个维度将行数乘以因子1（对于所有）加上维度中的成员数。

例如，我将具有性别等维度（男/女）和婚姻状况（已婚/单身），我将有以下行

男性单身中位数_风格
女性单身中位数_风格
所有性别单身middle_salary

男性已婚中位数_风格
女性已婚中位数_风格
所有性别已婚中位数_工资

男性所有婚姻中位数_风格
女性所有婚姻中位数_工资

>
所有性别所有婚姻中位数_工资

数据包含其他具有更高基数的维度但我在每个级别都有数字，而不是组合。例如，如果婚姻状况还包括"离婚"，我将为"离婚"获得额外的行。但
不是像"离婚或单身"，"离婚或结婚"等组合的指标。等等

>
我的挑战是将数据分类，以便人们可以报告。例如，如果报告不包含"性别"，我希望看到对应于"所有性别"的数字（工资），但如果数据包含性别列或过滤器
我想显示与相应性别相对应的数字。

此外，如果数据被过滤，例如更多成员但不是全部成员，我的维度应为空。 （即如果您按多个值过滤并选择单个并离婚，那么我希望看到该测量为空，因为我不知道正确的结果）

我想也许isFiltered函数可能会有所帮助，但我不太确定，我的力量BI / dax经验有限。

 

谢谢！

解决方案

我已经在Power BI论坛上发布了这个问题的答案  https://community.powerbi.com/t5/Desktop/Dax-Measure-question/td-p/686677

Hi all

I have a fact table based on data is already pre-aggregated at all levels. For privacy reasons I do not have access to the raw data.
Measures are things like count of people and median salary. Median salary is not really an additive measure and counts are neither (long explanation, is about some detailed data being hidden at very lower levels of granularity for privacy reasons).
Therefore I can't use any aggregate function to compute measures like averages, weighted averages, sums, they will have to be displayed the way they are stored.
My data will be a cross product of all dimensions members plus one "All" row for each dimension combination. Every dimension added to my data multiplies the number of rows by a factor of 1 (for all) plus the number of members in the dimension.
For instance, I will have dimensions like gender (Male/Female) and marital status (Married/Single), i will have the following rows

Male Single median_salary
Female Single median_salary
All genders Single median_salary
Male Married median_salary
Female Married median_salary
All genders Married median_salary
Male All Marital median_salary
Female All Marital median_salary
All genders All Marital median_salary

Data contains other dimensions with higher cardinality but I only have numbers available at each individual level, not for combinations. For instance if Marital status would also include "Divorced", I will get extra rows for "Divorced" but not metrics for combinations like "Divorced or Single", "Divorced or married" etc.
My challenge is to medel the data so that people can report on it. For instance if a report does not contain a "Gender" column I want to see the numbers (salary) corresponding to "All genders", but if data contains a Gender column or filter I want to show the numbers corresponding to the appropriate gender.
Also, if the data is filtered such as more members but not all are take, my dimension should be empty. (I.e. if you filter by multiple values and choose single and divorced then I want to see the measure empty as I don;t know the correct result)

I was thinking maybe the isFiltered function might help somehow, but I'm not quite sure, and my power BI / dax experience is limited.

 

Thanks!

解决方案

I've posted an answer for this over on the Power BI forum here https://community.powerbi.com/t5/Desktop/Dax-Measure-question/td-p/686677

这篇关于表格模型 - 建模问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！