Concatenate multiples facts and Aggr returning null

[Jsobrinho77]

Hi guys,

I have a data model with 3 concatenated tables, in some tables I have more or fewer fields than others, and I have a SOURCE field, this field has values ​​A, B, C.

I need to create a table with Products only in table A. So I created a dimension like:

aggr(Only({<SOURCE={'A'}> } Product),Product)

This set analysis returns null, so I need to uncheck the display of null values ​​in the dimension properties.

Is it correct to do this? I will need to insert 10 more columns. I will need to create an aggr(Only(..... for each column?

[vinieme12]

Nothing wrong with the calculated dimension

but when adding this to the chart did you Select "Dimension" or "Measure" ?

you need to select "Dimension"

Also since all your data is concatenated if your first dimension is filtered for Product source=A, when you add another column Product source =B  , you will see null values 

Can you post a screenshot of your Chart  on what you are trying to implement?

[Jsobrinho77]

first of all, thank you for your answer

I need to create a table with columns just from data source A, so I will include the columns Product, category, type (these columns are common for tables A, B, and C) so in the table, the dimensions Product, category and type... I need to use set analysis to show only data from data source A aggr(Only({<SOURCE={'A'}> } Product), Product). But Its showing the last row with null ( - ), so I need to uncheck the dimension properties "show null values".

My question, in this case, do I need to do aggr(Only... for others columns and uncheck "show null values", this is correct?

[vinieme12]

You won't need to do Aggr() for other fields ; restricting one field is enough 

[marcus_sommer]

I think you don't need to apply calculated dimensions to restrict the data to the wanted source else just applying the condition within the expressions, like:

sum({<SOURCE={'A'}> } Value)

will work, too. It depends on how many dimensions and expressions should be applied which method seems to cause less efforts but in my experience run conditions within expressions faster as in dimensions. Further only with native dimensions you will be able to use them within an aggr() or a total-statement to calculate/display more advanced views like an average on counts/sums or any kind of rates.

- Marcus

[Jsobrinho77]

My column is a dimension, not a measure, so I need to put Only({<SOURCE={'A'}> } Product), right? So I need to put aggr before:

aggr(Only({<SOURCE={'A'}> } Product),Product)

And my question is if, there is another way to 'filter' other columns to SOURCE A without aggr(Only({<SOURCE={'A'}> } Category),Category) or do I need do for all columns (dimension not measure )

[marcus_sommer]

You need to do it in all expressions or at least in one dimension by hiding any NULL's within the other dimensions. Both methods have benefits and disadvantages like hinted above.

- Marcus