top of page
Antares Studio Games Icon_Small.png

The Antares Alien

Power BI   |  Game Development

  • Writer's pictureBrent Jones

A Beginner's Guide to Automating Power BI with the Layout File (Part 3)

In this article, we will discuss how to change the measure of a Power BI visual programmatically with Python. This is the third part, so if you are unsure what the Layout file is please see part 1. Part 2 goes over how to access a visual in the Layout file with Python.

All measures have two parts to them; the measure name, and the measure home table. For example, a measure called "Total Sales" is stored in the "financials" table.

The Layout file references measures in numerous places in the "config", "dataTransforms" and "query" key-values by a combination of the measure name, measure table, or combined measure.table syntax; i.e. "financials.Total Sales". Because the config JSON is lengthy, I won't show the entire structure, but instead I will show it as a Python dictionary where measures are being stored/referenced. (Brace yourself! It's a lot.) As a reminder part 1 shows you how to get the Layout file and load it into Python.

Measures in the config json

# Measure references in config:

# Table.Measure

# Table

# Measure

# Table.Measure

# Measure

Measures in the dataTransforms json

# Measure references in dataTransforms:

# Table

# Table.Measure

# Measure

# Measure

# Table.Measure

# Table

# Measure

Measures in the query json

# Measure references in query:

# Table

# Measure

# Table.Measure

Wow that's a lot! Keep in mind, though, if your measures are all hosted in the same table, you don't need to worry about the table references. Knowing the structure of the JSON and where the measure is being referenced allows us to change the measure programmatically. Let's take a look how to do that next.

Changing the measure name with Python

It will be best to create three separate functions for handling the 'config', 'dataTransforms' and 'query' operations.

# Measure references in config:
def ChangeMeasureInConfig(tablename, measurename):
    fullname = tablename + '.' + measurename
    config['singleVisual']['projections']['Values'][0]['queryRef'] = fullname # Table.Measure
    config['singleVisual']['prototypeQuery']['From'][0]['Entity'] = tablename # Table
    config['singleVisual']['prototypeQuery']['Select'][0]['Measure']['Property'] = measurename # Measure
    config['singleVisual']['prototypeQuery']['Select'][0]['Name'] = tablename # Table.Measure
    config['singleVisual']['prototypeQuery']['OrderBy'][0]['Expression']['Measure']['Property'] = measurename # Measure
    firstvisual['config'] = json.dumps(config)

# Measure references in dataTransforms:
def ChangeMeasureInDataTransforms(tablename, measurename):
    fullname = tablename + '.' + measurename
    datatransforms['queryMetadata']['Select'][0]['Restatement'] = tablename # Table
    datatransforms['queryMetadata']['Select'][0]['Name'] = fullname # Table.Measure
    datatransforms['queryMetadata']['Filters'][0]['expression']['Measure']['Property'] = measurename # Measure
    datatransforms['selects'][0]['displayName'] = measurename# Measure
    datatransforms['selects'][0]['queryName'] = fullname # Table.Measure
    datatransforms['selects'][0]['expr']['Measure']['Expression']['SourceRef']['Entity'] = tablename # Table
    datatransforms['selects'][0]['expr']['Measure']['Property'] = measurename # Measure
    firstvisual['dataTransforms'] = json.dumps(datatransforms)

# Measure references in query:
def ChangeMeasureInQuery(tablename, measurename):
    fullname = tablename + '.' + measurename
    query['Commands'][0]['SemanticQueryDataShapeCommand']['Query']['From'][0]['Entity'] = tablename # Table
    query['Commands'][0]['SemanticQueryDataShapeCommand']['Query']['Select'][0]['Measure']['Property'] = measurename # Measure
    query['Commands'][0]['SemanticQueryDataShapeCommand']['Query']['Select'][0]['Name'] = fullname # Table.Measure
    firstvisual['query'] = json.dumps(query)

NOTE: Remember to dump the json back into a string for each 'config', 'query' and 'dataTransforms'! i.e. the query is dumped like this: firstvisual['query'] = json.dumps(query). I've placed these at the end of each method definition in my code above.

Some fields may not exist

Depending on your specific visuals' layout, some of the fields above may not exist. This is because Power BI uses a 'Just-In-Time' approach to building and maintaining the visual JSON structure. For example, if your visual has no filters on it, one or all of the properties pertaining to filters may not exist until you place a filter on it. For this reason, it's a good idea to first check if the property exists or not; either using a try: except: block or some other implementation that works for your project. This article here has several examples of checking for this (python):

If your code successfully runs, go through the process of building your PBI file back together, and you should see your visuals updated accordingly. Refer to part 1 for this process.

Great job!

This concludes my beginners guide to automating Power BI visuals using the Layout file and Python. Enjoy!

  • Facebook
  • Twitter
  • Instagram
bottom of page