Keys Field

On this page, you will learn what keys field should contain and how to configure correctly.

Introduction

The keys field should include identification keys to send data to the database.

Place keys into the Keys box. These keys will not be editable or visible. They represent the identification of each cell, enabling data storage in the database. If a column with the same name exists in the table, any changed measure values along with the keys will be written to the writeback table. If the keys do not already exist, both the keys and measure values will be inserted. If the keys already exist, only the measure values will be updated.

An example is provided in the next image. In the image, you can see the names of the keys in Power BI, each of which has a matching column in the database. This ensures that all the changes you make will be saved.

How to create keys effectively

  1. Create a new measure in the measure table with your desired name.
  2. Set up a DAX measure, for instance: ProductKey = MIN(Products[Product Code]). Once you choose an operator, it must remain consistent for that specific measure to ensure data persistence.

    Important, when you are configuring the key measure you need to ensure the Format of the key measure is set the same type that is in the database, for example if you have a key that is a string, you need to ensure in Power BI the format is set to Text, in the next image is represented an example.

    If it is a number you need to set on Power BI the Data type to one of this three options and set the Format to Whole number or Decimal number, in the next image is represented an example.

    If it is a BIT on the database you need to set the type on the Power BI to be a boolean that is represented like this True/false, in the next image is represented an example.

    If it is a date or datetime or time on the database you need to set on Power BI to one of this data types that is shown in the next image.

    Note: If your database contains a date that is not in the default format (date: YYYY-MM-DD, datetime: YYYY-MM-DD HH:mm:ss, time: HH:mm:ss), you need to ensure that the corresponding key in Power BI is Text data type and matches the format used in your database.

  3. After placing it in the visual, you can rename the key. If the key's name doesn't match the one in the database, rename it in the visual to align with the database name. For example, if the name in the database is "Product" and the name in Power BI is "ProductKey", rename the measure in the visual to "Product" to ensure that the data is saved correctly in your database. Failure to rename the measure in Power BI may result in data not being saved. Therefore, it is important to ensure that the measure name in the visual matches the database name for accurate data saving.

Updated over a month ago