Values present in Fact table but missing in Dimension table not showing in visual

[Vivdroid_C_4222]

I have a Fact Table imported via DirectQuery, which contains employee-wise sales values.

I also have a Dimension Table that contains all the employee names, and it's connected to the Fact Table via a common column called UserId.

There are a few UserIds present in the Fact Table that are not present in the Dimension Table.

When I create a visual to show employee-wise sales, the visual does not display data for employees who are missing from the Dimension Table.

However, when the same tables are imported using Import mode, the unmatched UserIds from the Fact Table are shown under a (Blank) category in the visual.

Why is this not happening in DirectQuery mode?
How can I fix this and show all sales, even if employee names are missing?

[Anonymous]

Hi @Vivdroid_C_4222 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[Vivdroid_C_4222]

No solution mentioned below has worked, hence I  have not marked any answer as a solution

[Anonymous]

Hi @Vivdroid_C_4222 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

[Anonymous]

Hi @Vivdroid_C_4222 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Anonymous · Accepted solution]

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.

[mdaatifraza5556]

Hi @Vivdroid_C_4222 

Power BI handles relationships differently in DirectQuery mode compared to Import mode, especially for missing dimension values:

• In Import mode, Power BI loads all data into memory and performs a left outer join between Fact and Dimension.

• In DirectQuery mode, Power BI sends SQL queries directly to the underlying database. When you build a visual that includes fields from both tables (like EmployeeName from Dimension and Sales from Fact), the SQL query it generates uses INNER JOIN by default.
  
  
  To filx this you can try this--- Create calculated column in fact table using below dax
  
  Employee Display Name =
  IF(
  ISBLANK(RELATED('DimEmployee'[EmployeeName])),
  "Unknown",
  RELATED('DimEmployee'[EmployeeName])
  )
  
  
  If this answers your questions, kindly accept it as a solution and give kudos.

[Vivdroid_C_4222]

we cant create calculated columns in Direct Query table

[johnt75]

The blank row is created automatically on the one-side of a regular relationship when there are rows in the many-side without a corresponding entry on the one-side. This doesn't happen for limited relationships, like when the many-side table is in Direct Query mode, as the engine doesn't know whether any entries on the many-side are missing values on the one-side.

One possible workaround would be to add an extra row to the employees table with a name like "Missing Data" and a blank employee id.