What is Preserve Grain?

By default a dataset's grain is determined by the table with the lowest grain, but you can use the preserve grain setting to specify which table determines the dataset's grain.

See Specify Which Table Determines Grain.

You can create a measure in any table in a dataset. However, this can cause the measure on one side of a one-to-many or many-to-many relationship to be duplicated. In cases like this you can set the table on one side of the cardinality to Preserve Grain to keep its level of detail.

For example, if you have an Orders table and an Order Items table, join them on the Order ID column, and query them, then the Order Total is duplicated for each order item. This is because the Order Items table has the lower grain.

Description of the illustration data_set_editor_grain_order_example_before.jpg

However, if you want the query results to show data at the Orders table's level, then in the Dataset Editor's Data Diagram, you set the Orders table to Preserve Grain.

Note:

In the following example, the Product ID column is NULL because there are multiple values for PRODUCT for each Order. The Values are set to NULL to preserve the grain at the Order level.

Description of the illustration data_set_editor_grain_order_example_after.jpg