cancel
Showing results for 
Search instead for 
Did you mean: 

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

TomerA
8 - Cloud Apps
8 - Cloud Apps

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.

1 ACCEPTED SOLUTION

wallingfordce
8 - Cloud Apps
8 - Cloud Apps

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.

View solution in original post

4 REPLIES 4

harikm007
12 - Data Integration
12 - Data Integration

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.

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

wallingfordce
8 - Cloud Apps
8 - Cloud Apps

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.

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