17 Mar Defining Relationships Between Entities In The Common Data Model To Automatically Create Relationships In Power BI
Following on from my last post on attaching manually-created Common Data Model folders in Power BI dataflows, I was asked whether defining relationships between entities in the model.json file of the CDM folder results in relationships being created between tables in a Power BI dataset. I’ve just tested it, and I can confirm it does.
Consider a CDM folder that contains two entities, Sales and Fruit. The Sales entity contains monthly sales data:
…and the Fruit entity contains a list of fruit sold:
Now –and this is important – let’s say you have defined the entities in the model.json so that the Sales entity has attributes Month, Product and Sales:
…and the Fruit entity has a single attribute called Fruit:
Because the Sales entity and the Fruit entity use different names for the attributes that contain the names of the fruit sold, when the two entities are loaded into Power BI no relationships are created between the two tables. There’s no way that Power BI can guess that there should be a relationship between the Product and Fruit columns based on these names.
However, if you add a relationship definition (as documented here) to the model.json file like so:
Then, when you load the two entities as tables in the same Power BI dataset, you get a relationship created automatically:
Now if we could add DAX calculations (calculated columns and especially measures) to the definition of the model.json file, so they were automatically created when entities were imported from a dataflow, that would be really cool. I don’t see why this would not be possible because you could store all kinds of useful information – such as the DAX for the calculations – in the metadata record of an M query (you can access this yourself using the Value.Metadata M function) loaded into a dataset, and the Power BI engine could read it when the table is loaded and create the calculations from this.