Part 1 – DAX scripting

There is a data model. This data model consists of one (1) dimension table and three (3) fact tables. Each fact table consists of one (1) column with the dimension values next to it with a calculation column with revenue, costs, discount.

For each fact table, the revenue, costs and discount must be calculated separately. This can be done by creating a measure nine (9) times. This is not efficient. Certainly not if the model does not consist of three (3) fact tables, but, for example, nine (9). With nine (9) fact tables, eighteen (18) measures will have to be created. Using Tabular Editor 3 – DAX scripting can do this much more efficiently…
Start

• Create a measure table in the semantic model. This is a table in which all measures can be placed. In addition to providing clarity, it also ensures an efficient working method.

• Create the measures associated with the first fact table.

• Place these measures in a folder. Tip! Name the folder after the fact table to which these measures belong.

Tabular Editor 3

• Open Tabular Editor 3 – connect to your (local) data model.

• Select the created folder with the created measures in the measure table. Tip! These can be found in the TOM Explorer.

• Right Mouse Button + Script DAX

Tabular Editor 3 has now put all the selected measures together

Replace value

• CTRL + A to select all

• CTRL + H to change lookup values

• Replace All

• Change the old values (old fact table) to the new values (new fact table)

Tabular Editor 3 has now created a script to copy and paste
Finish

• Press F5

• CTRL + ALT + S to save the change back to the model.

Tabular Editor has now copied all measures (including folder) and created them as new.

Sharing is caring!

Have a look at my other blogs

ISFILTERED

You probably know that the ISFILTERED function returns TRUE when the specified table or column is being filtered directly. But did you know that if you add multiple ISFILTEREDs together, ISFILTERED will not return true or false, but will return a number. You can combine this returned number with a switch function to influence your total levels.

Un-Bold Row Headers

Have you ever created a matrix visual, with hierarchy, where your row headers are formatted 𝐛𝐨𝐥𝐝? You can not seem to turn if off, because

Alignment of visuals

Have you ever heard of the “Align” button? It helps you to align visuals, no more precision drag-and-drop…

It also helps to distribute visuals horizontally of vertically over a canvas. This will give an overall alignment of visuals which would make your report so much cleaner.