Show blank values in pivot table instead of not showing line at all

[TomerA]

Hi,
In the data model I have main table that is linked to another table using an index.
Not all rows exists on the other table.
When I try to display in the Pivot table column from the other table it omits from the table the non linked rows.
Is there a way to display all rows even if not exists in the other table? 
I know I can do data model manipulation such as Join tables, but I wish to solve this at the dashboard level.

[harikm007]

Hi @TomerA ,

I think the best method is updating the data model. But you can do it by using 'ALL' function in formula.  

For example, if you have 2 tables - Table1 and Table2. Table1 is a DIM table which contains 5 Names and their IDs. Table2 is a transaction table which contains only 3 Names. Tables are connected using Name field. Below is the formula to display Id from DIM table, Name from DIM table, Sum of values from transaction table.  

case when (sum([Value]), all([Name]), all([id])) > 0 then (sum([Value])) else 0 end

In summary you need to use 'ALL' function with all fields from DIM table that are used in pivot table.

Please refer screenshots.

[TomerA]

Hi, Thanks for the reply. Unfortunately the column I add is Row and not Value so I cannot use formulas.

[wallingfordce · Accepted solution]

Hi @TomerA -

Sisense requires that either all main table values exist in the other table, or you need to create an intermediary/bridge table with all distinct values from the main table left joined to the other table, and a null replacement for the main table values that don't exist in the other table.

[TomerA]

Thanks, I thought there way to avoid this.
To solve this I created a custom column with relevant missing data in the main table.