VALUES Function as a Table

DAX DAX Calculations
1

Hi All

I am trying to calculate the average sales by store as a measure where the sales team person is my initial filter context. I recall seeing a video that I should only iterate a fact table with VALUES and not the dimension table. In the attached PBI file, I have created 2 measures and both calculate the same answer.

Challenge 1 VALUES.pbix (943.7 KB)

Is there a correct method on whether the fact or dimension table should be used with VALUES ?
Based on my understanding, wouldn’t the dimension table be better as it would provide a list of all the possible stores? If I use the fact table, the VALUES list would miss out on a store with no sales.

Many thanks!
SW

2

@swong12

  1. You use VALUES over the column from the fact table

If it is a flat model with no dimensions then you have the access to all the columns
If it is a start/snow flake schema then you only have access to the Foreign key

In this situation you can “Optimize” the code by a bit because the DAX engines dont have to pay the price of the relationship

In SQL terms it would be

SELECT
    ForeignKey, 
    [Total Sales] = SUM ( TotalSalesColumn ) 
FROM
    Fact 
GROUP BY
    ForeignKey

Question you should ask yourself - Do I need any other column from the dimension table? If not then continue with VALUES ( Fact[Column] ) approach.

  1. You use VALUES over the column from the Dimension table

If data model isn’t built correctly and if there are rows in fact table for which there are no corresponding rows in the Dimension table, then VALUES will return a Blank row for consolidating all those line items that don’t have a match. In this scenario if you use VALUES over fact table then the number you show will be less than actual value.

In this scenario you have access to all the columns, maybe you need some different column to apply a filter and can’t use the primary key, in that case you must use VALUES with that column.

You must pay the price of the relationship/join, how much? depends on how large your dimension is and how many values participate in the relationship.

In SQL terms it would be

SELECT
    F.ForeignKey, 
    [Total Sales] = SUM ( F.TotalSalesColumn ) 
FROM
    Fact AS F
        LEFT JOIN Dimension AS D
                ON F.ForeignKey = D.PrimaryKey
GROUP BY
    F.ForeignKey

Since DAX Engine keeps the Fact table on the left side of the query, you only get the rows that match in the Fact so in most of the measures that you will write, you won’t have to worry about this. If you are creating a Calculated Table then you may need those values.

3 Likes