User Defined Functions is a new feature in PowerBI currently in public preview. There is a lot of buzz in the air regarding this because it opens new scenarios with modularity and reusability of the code.
For more details about DAX UDF’s you can check for example following blogs posts:
🔗 Introducing user-defined functions in DAX
by Marco Russo & Alberto Ferrari from SQLBI
🔗 8 tips for writing User-Defined Functions in DAX
by Kurt Buhler from Tabular Editor
As mentioned in second article, one very important aspect of modularity in code is Testing and that’s where CAT comes in. We are evangelists of Power BI testing for a long time and now this topic becomes even more important!
We are hearing all the time Testing is Difficult and expensive. But guess what, it is not anymore. You can have your PowerBI functionality under the tests in few minutes.
So how CAT can help you with a testing of your Power BI model and especially new UDF functionality?
UDF are in preview so first you need to enable this preview feature: Power BI -> File -> Options and Settings -> Options -> Preview Features ->”DAX user defined functions”
Now let’s do a simple new UDF which calculates percentage from Product Category and Productsub-Category perspective. We will use function in % version of the measures.
Percentage measure definition using function:
Here is a simple test table report:
Ok so I have my report. How do I put this functionality under repeatable tests in 10 minutes?
1) Get CAT Studio from app store.
2) Open CAT Studio and Create Data source – Connection to your open Power BI desktop Report. (Very simple CAT studio will show you list of open PB Reports)
3) Create Test and Write test queries (I have a test query in my tables in screenshot done already, right?). In our case, I’ll create two tests.
a) Usage of the Function by Product Category
b) Usage of the Function by Product Subcategory
4) Run tests
5) Refactor Function
6) Run tests
7) Refactor Function
8) …
Hard to imagine?
Here is an example of two simple test:
You might think this is a silly test. Of course to write a clever and simple tests is not always trivial. CAT can do very good work for regression testing of Power BI but more on that maybe in next blog.
Now after seeing a relationship between Product Category and Product Subcategory In my model:
I might think maybe I can optimize function and remove filter only from Product Subcategory so I change my function like this:
Question for you: Will this function still work correctly when used together with Product Category?
To test it I can just re-run my CAT tests which compare the results of two queries, and I have my answer in 2 seconds!
About the Author
I help organizations build trust in their data by making automated data testing a natural part of every project. At CAT, I work closely with corporate data leaders and our partners to ensure strategies, platforms, and teams rely on reliable, validated data – whether in banking, insurance, e-commerce, AI initiatives or F1 team. With more than 15 years of experience in the data field, I combine technical expertise with business insight to guide clients, mentor teams, and contribute actively to the data quality community.
