Data Model Question

[Lily36876]

I have a dimension table named "Store List" that is linked to both the sales details line and the store sales target table.

However, in the past few days, when I use the fields from the "Store List" dimension table and the sales details to create a pivot table, some stores are missing data.

I discovered that this is because they do not appear in the store sales target table.

The perplexing part is that my pivot analysis table has no direct connection to the store sales target table whatsoever.

I haven't utilized any fields from the store sales target table.

Is there a setting I might have missed?

Sample1.

Store disconnect with sales target table , and Shop H011 has Last Year Total Sales in 2023

and  my LYTTL DAX is :

LYAMT = CALCULATE('Sales Line'[TTL],SAMEPERIODLASTYEAR('Date Table'[Date]))

Sample2.

Store connect with sales target table , and Shop H011 lost Last Year Total Sales in 2023

because this shop doesn't have sales target.

but in this pivot table, I don't use any columns from Sales Target Table.

Please help me....

In the past, when working with pivot tables, if a field in the fact table had no data, it would typically result in empty cells at most. I have never encountered a situation like this before.

[Anonymous]

Hi @Lily36876 ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[OwenAuger]

Hi @Lily36876 

Could you please post your data model diagram, and the definition of the TTL measure?

Or a sanitised PBIX if possible.

This behaviour suggests that their might be a bi-directional relationship somewhere in the model (Cross filter direction = "both"). I would suggest ensuring all relationships are single-diretional (Cross filter direction = "single").

Regards