Forum Discussion

PowerFabian's avatar
PowerFabian
Frequent Visitor
5 years ago

Caluclated Column returning empty value

Hello,

I have an calculated column that is returning an empty value for an row and i am not quite understanding why. What I am trying to do with this calculated column is to returning the sum of another table (called 'Planzahl') with some specific parameters (year, quarter, employee id).
I filtered the table (the table is called 'Lieferantenprämie Hilfstabelle') for an example of the blank value:

My formula is as follows: (For better understanding: Jahr=year, Quartal= quarter, Mitarbeiter ID= employee ID)

Individualprämie =
CALCULATE(
Sum('FACT_Individualprämie'[Planzahl]),
Filter(DIM_Jahr, DIM_Jahr[Jahr]='Lieferantenprämie Hilfstabelle'[Jahr]),
Filter(DIM_Quartal, DIM_Quartal[Quartal]='Lieferantenprämie Hilfstabelle'[Quartal]),
Filter(DIM_Mitarbeiter, DIM_Mitarbeiter[Mitarbeiter ID]='Lieferantenprämie Hilfstabelle'[Mitarbeiter ID])
)


I don't understand the blank value, because all columns used in my formual are the same for this rows in my example as you can see here:

 

 

But for one of these rows, my formula is returning an empty value.

Relation between DIM_tables and the table FACT_Individualprämie_
DIM_Jahr[Jahr]                               1:N   'FACT_Individualprämie'[Jahr]

DIM_Quartal[Quartal]                    1:N   'FACT_Individualprämie'[Quartal]

DIM_Mitarbeiter[Mitarbeiter ID]   1:N    'FACT_Individualprämie'[Mitarbeiter ID]

 

Does anyone know why or has an idea how to solve this?

 

Thanks and regards,

Fabian

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Consider two tables: 1 Fact Table and another Dimension Table


    In this eg, we have a Product Lookup Table containing 3 Product ID (12,13,14). But our Fact Table (Sales By Store) contains additional product IDs NOT included in the Product Lookup (15,16)

     

    • Instead of throwing an error when a value is missing from a lookup table, the DAX engine adds a BLANK row (which will appear in visuals when missing values are present)
    • Different Table Functions handle the presence of this Blank row differently.
      For eg., VALUES will always show the blank row but DISTINCT will not.

    Note: If you think you might have missing values in your Lookup Table (or aren't sure), use VALUES to look. 
    Values have some built-in logic that will return BLANK row if there's a value that is found on the MANY side of the relationship & not on the ONE side.

     

     

    Kudos if this works for you. Thank you