Forum Discussion
Values present in Fact table but missing in Dimension table not showing in visual
Hi Vivdroid_C_4222
Thank you for reaching out microsoft fabric community forum.You're absolutely right — in DirectQuery mode, Power BI doesn't allow creating calculated columns in the table. That limitation can make it tricky to handle missing employee names.
One option is to use a measure instead. You can create a measure that checks whether the employee name exists and shows “Unknown” if it doesn't. For example:
Employee Sales Display :=
SUMX (
VALUES ( 'FactSales'[UserId] ),
VAR EmployeeName = LOOKUPVALUE('DimEmployee'[EmployeeName], 'DimEmployee'[UserId], 'FactSales'[UserId])
VAR DisplayName = IF ( ISBLANK(EmployeeName), "Unknown", EmployeeName )
RETURN
CALCULATE ( SUM ( 'FactSales'[SalesAmount] ) )
)
You can then use this measure in a visual like a table or matrix, and it will show known employees normally, and group any unmatched UserIds under "Unknown".
Alternatively,If you have access to the data source (like SQL Server), another option is to create a view that handles this logic at the source using a left join. That way, Power BI will just read the result as-is, including any missing employees shown as "Unknown".
If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.
Hi Vivdroid_C_4222
Thank you for reaching out microsoft fabric community forum.
You're absolutely right — in DirectQuery mode, Power BI doesn't allow creating calculated columns in the table. That limitation can make it tricky to handle missing employee names.
One option is to use a measure instead. You can create a measure that checks whether the employee name exists and shows “Unknown” if it doesn't. For example:
Employee Sales Display :=
SUMX (
VALUES ( 'FactSales'[UserId] ),
VAR EmployeeName = LOOKUPVALUE('DimEmployee'[EmployeeName], 'DimEmployee'[UserId], 'FactSales'[UserId])
VAR DisplayName = IF ( ISBLANK(EmployeeName), "Unknown", EmployeeName )
RETURN
CALCULATE ( SUM ( 'FactSales'[SalesAmount] ) )
)
You can then use this measure in a visual like a table or matrix, and it will show known employees normally, and group any unmatched UserIds under "Unknown".
Alternatively,If you have access to the data source (like SQL Server), another option is to create a view that handles this logic at the source using a left join. That way, Power BI will just read the result as-is, including any missing employees shown as "Unknown".
If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.