Could you please elaborate more?I have created a cube based on 'Sales' persistent class. Then I created an hierarchy and added a Branch level based on Branch property. Then I compiled the class. After that I used Studio to manually add members to that level and built the cube. I removed the 'NON EMPTY' part of the MDX query.As a result, now it only show facts for branches that are specifically mentioned in 'Members' tag of the cube definition AND have a matching fact in the table.
Assuming a table like this (omitting the ID column and some others)
And I need to show the amount of sales in 2021 with the result like this:
Where Northwest matches sales that happened either in a Northern or Western branches, Oriental matches sales that happened in Eastern branches, and Southern matches sales that happened in Southern branches.
Union seems to achieve what I want.
I am unsure what did you mean by using IN though.
The problem I was facing was aking to this:I have a table called 'Sales' with a column called 'Branch' which holds the data of where the sale occured. I need to show all sales that occured in a specific time period for all branches, even if a branch had zero sales. The catch is there is no separate table that holds Branches so there's nothing I can join with AND I need to modify the values of the column 'Branch' a bit (e.i. some branches should be considered as one, some branches should have their name changed, etc.).
So the best possible solution I could find was to use Table Value Constructor to create the list of needed branches in the query and join it with a partially filtered table to get those Branches with 0 sales.
Log in or create a new account to continue