{"id":515,"date":"2024-12-07T16:13:31","date_gmt":"2024-12-07T16:13:31","guid":{"rendered":"https:\/\/datadandies.nl\/?p=515"},"modified":"2024-12-08T09:05:39","modified_gmt":"2024-12-08T09:05:39","slug":"creating-multiple-measures-for-a-power-bi-semantic-model-at-once-using-c-scripts-in-tabular-editor-2-0","status":"publish","type":"post","link":"https:\/\/datadandies.nl\/index.php\/2024\/12\/07\/creating-multiple-measures-for-a-power-bi-semantic-model-at-once-using-c-scripts-in-tabular-editor-2-0\/","title":{"rendered":"Creating multiple measures for a Power BI Semantic model at once using C# scripts in Tabular Editor 2.0"},"content":{"rendered":"\n

Imagine you have to create a whole lot of measures in Power BI that are similar.<\/p>\n\n\n\n

One free way to do this is, is by using C# scripts in Tabular Editor 2.0.<\/p>\n\n\n\n

Below is an example of a \u201cstatic\u201d way to do this. You simply make the necessary adjustments like changing the table name and a measure will be added according to the logic of the C# script below. However, what if you would like to make this script a little more dynamic?<\/p>\n\n\n\n

Static:<\/p>\n\n\n\n

<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
\/\/Static: create a measure called "Total TalkTime" in Display Folder ..<\/span><\/span>\n\/\/.. called "MeasureFoldersStatic" for the column column_name in table FactCalls<\/span><\/span>\n<\/span>\nvar<\/span> c <\/span>=<\/span> <\/span>(<\/span>Model<\/span>.<\/span>Tables<\/span>[<\/span>"<\/span>FactCalls<\/span>"<\/span>].<\/span>Columns<\/span>[<\/span>"<\/span>TalkTime<\/span>"<\/span>]<\/span> <\/span>as<\/span> DataColumn<\/span>)<\/span>;<\/span>  <\/span>\/\/Table and column <\/span><\/span>\n<\/span>\nc<\/span>.<\/span>Table<\/span>.<\/span>AddMeasure<\/span>(<\/span>                                 <\/span>\/\/Adding a measure<\/span><\/span>\n        <\/span>"<\/span>Total <\/span>"<\/span> <\/span>+<\/span> <\/span>c<\/span>.<\/span>Name<\/span>,<\/span>                          <\/span>\/\/Name of the measure<\/span><\/span>\n        <\/span>"<\/span>SUM(<\/span>"<\/span>  <\/span>+<\/span> <\/span>c<\/span>.<\/span>DaxObjectFullName<\/span> <\/span>+<\/span> <\/span>"<\/span>)<\/span>"<\/span>,<\/span>        <\/span>\/\/DAX of the measure<\/span><\/span>\n        <\/span>c<\/span>.<\/span>DisplayFolder<\/span> <\/span>+<\/span> <\/span>"<\/span>MeasureFoldersStatic<\/span>"<\/span>    <\/span>\/\/Folder where the measure will be stored <\/span><\/span>\n        <\/span>)<\/span>;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The above example works well for a single measure, what if you need something more dynamic? Let\u2019s look at a script that can handle multiple columns.<\/p>\n\n\n\n
Then you could try the script below. This script will create a SUM of every column you have selected. Very interesting stuff.<\/p>\n\n\n\n
Based on which columns you select:<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
\/\/Based on selected columns: create measures starting with "Total " in Display Folder ..<\/span><\/span>\n\/\/.. called "MeasureFolderSelected" for the selected columns <\/span><\/span>\n<\/span>\nforeach<\/span>(<\/span>var<\/span> c <\/span>in<\/span> <\/span>Selected<\/span>.<\/span>Columns<\/span>)<\/span>                  <\/span>\/\/Variable with selectec columns<\/span><\/span>\n{<\/span><\/span>\n    <\/span>c<\/span>.<\/span>Table<\/span>.<\/span>AddMeasure<\/span>(<\/span>                             <\/span>\/\/Adding a measure<\/span><\/span>\n        <\/span>"<\/span>Total <\/span>"<\/span> <\/span>+<\/span> <\/span>c<\/span>.<\/span>Name<\/span>,<\/span>                          <\/span>\/\/Name of the measure<\/span><\/span>\n        <\/span>"<\/span>SUM(<\/span>"<\/span>  <\/span>+<\/span> <\/span>c<\/span>.<\/span>DaxObjectFullName<\/span> <\/span>+<\/span> <\/span>"<\/span>)<\/span>"<\/span>,<\/span>        <\/span>\/\/DAX of the measure<\/span><\/span>\n        <\/span>c<\/span>.<\/span>DisplayFolder<\/span> <\/span>+<\/span> <\/span>"<\/span>MeasureFolderSelected<\/span>"<\/span>   <\/span>\/\/Folder where the measure will be stored<\/span><\/span>\n        <\/span>)<\/span>;<\/span><\/span>\n}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Inspiration source: https:\/\/www.youtube.com\/watch?v=EHs5r3XCkO8<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Imagine you have to create a whole lot of measures in Power BI that are similar. One free way to do this is, is by using C# scripts in Tabular Editor 2.0. Below is an example of a \u201cstatic\u201d way to do this. You simply make the necessary adjustments like changing the table name and…<\/p>\n
Read More<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[51,3,50],"class_list":["post-515","post","type-post","status-publish","format-standard","hentry","category-blog","tag-c","tag-powerbi","tag-tabulareditor"],"_links":{"self":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/515","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/comments?post=515"}],"version-history":[{"count":2,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/515\/revisions"}],"predecessor-version":[{"id":519,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/posts\/515\/revisions\/519"}],"wp:attachment":[{"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/media?parent=515"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/categories?post=515"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/datadandies.nl\/index.php\/wp-json\/wp\/v2\/tags?post=515"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}