Forum Discussion

TomerA's avatar
TomerA
Cloud Apps
12-28-2021
Solved

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

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.

dashboards & reporting
  • wallingfordce's avatar
    wallingfordce
    01-10-2022

    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.

4 Replies

Replies have been turned off for this discussion
  • wallingfordce's avatar
    wallingfordce
    Data Pipeline
    01-10-2022

    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's avatar
      TomerA
      Cloud Apps
      01-11-2022

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

  • harikm007's avatar
    harikm007
    Data Warehouse
    12-28-2021

    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's avatar
      TomerA
      Cloud Apps
      01-02-2022

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