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!
