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
config['singleVisual']['projections']['Values'][0]['queryRef']
# Table
config['singleVisual']['prototypeQuery']['From'][0]['Entity']
# Measure
config['singleVisual']['prototypeQuery']['Select'][0]['Measure']['Property']
# Table.Measure
config['singleVisual']['prototypeQuery']['Select'][0]['Name']
# Measure
config['singleVisual']['prototypeQuery']['OrderBy'][0]['Expression']['Measure']['Property']Measures in the dataTransforms json
# Measure references in dataTransforms:
# Table
datatransforms['queryMetadata']['Select'][0]['Restatement']
# Table.Measure
datatransforms['queryMetadata']['Select'][0]['Name']
# Measure
datatransforms['queryMetadata']['Filters'][0]['expression']['Measure']['Property']
# Measure
datatransforms['selects'][0]['displayName']
# Table.Measure
datatransforms['selects'][0]['queryName']
# Table
datatransforms['selects'][0]['expr']['Measure']['Expression']['SourceRef']['Entity']
# Measure
datatransforms['selects'][0]['expr']['Measure']['Property']Measures in the query json
# Measure references in query:
# Table
query['Commands'][0]['SemanticQueryDataShapeCommand']['Query']['From'][0]['Entity']
# Measure
query['Commands'][0]['SemanticQueryDataShapeCommand']['Query']['Select'][0]['Measure']['Property']
# Table.Measure
query['Commands'][0]['SemanticQueryDataShapeCommand']['Query']['Select'][0]['Name']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): https://www.scaler.com/topics/check-if-key-exists-in-dictionary-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!
