Caluclated Column returning empty value

PowerFabian — Mon, 15 Mar 2021 14:27:38 GMT

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

Re: Caluclated Column returning empty value

Anonymous — Fri, 04 Jun 2021 04:13:55 GMT

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

topic Caluclated Column returning empty value in DAX Commands and Tips

Caluclated Column returning empty value

Re: Caluclated Column returning empty value