When to use Dimension Date and Data Date

[Ding]

Hi Everyone,

I have two question about when to use Dimension Date and Data Date. Looking for your kindly help.

Question 1:

I have Dimension Table: Dimdate(Primary Key- Display Date), Staff Table(Primary Key- Staff)

Fact Table: Sale(Foreigh Key-Sales Date), Activity(Foreigh-Activity Date)

 

Measure: Moving Average in Sales Table.

When I use Sales Date in sales table, return me correct moving average, but when I use display date from Dim table, return me incorrect value. Why it give me incorrect value? The reason I need use Dim Date is because I need it filter value for other visual.

Question 2:

When I click display date to filter , why it didnt return me Count of Type by staff.

[v-yuta-msft]

Hi Ding,

For Q1, you drag [DisplayDate] from 'mdw dimDate' to your chart, right? Because the relationship between 'mdw dimDate' and 'Sales' is one-to-many, so column [DisplayDate] will filter your chart first, then your measure will be calculated based on the filter context. So the average is calcualted in a filtered table which have only one value in [Sales Value] column.

For Q2, please check if values in [staff] column are numeric value, it not, those values only can be counted.

Regards,

Jimmy Tao

[Ding]

Thanks Jimmy.

That means we can't use measure for aggregation when we use data from Dimension Table and we can't use calculated column for aggregation.

So what is the solution for using arregation and use it to return the value on different visual?