Automated Unit Testing

dritsche — Sun, 12 Nov 2023 00:15:14 GMT

Hi Forum,

Inspired by this article, I'd like to create automated unit tests to test measures within my Power BI Semantic Model (Dataset).

I'm planning to:

- Use Powershell to connect to and run through the tests

- DAX scripts to query the Semantic Model itself

Since these are unit tests, I want to contain "fake" data within the test itself (not have to worry about the underlying database changing).

A few approaches I've attempted.

1. Overwrite Existing Table for Test (Doesn't work; PBI won't let me overwrite the actual_table in the semantic model)

DEFINE TABLE actual_table = UNION(ROW("order","A1"),ROW("order","B1")) -- attempt to update as a "local variable" MEASURE 'Measures Table'[COUNT_ORDERS] = DISTINCTCOUNT('actual_table'[order])) EVALUATE ROW("COUNT_ORDERS",'Measures Table'[COUNT_ORDERS])

Error: Table '<ccon>actual_table</ccon>' cannot be created because a table or variable with the same name already exists.

2. Update Table Reference (Pretty sure PBI was designed purposely not to do this)

DEFINE TABLE test_table = UNION(ROW("order","A1"),ROW("order","B1")) MEASURE 'Measures Table'[COUNT_ORDERS] = DISTINCTCOUNT('actual_table'[order])) -- somehow update table reference 'actual_table' to 'test_table' EVALUATE ROW("COUNT_ORDERS",'Measures Table'[COUNT_ORDERS])

3. SWITCH between actual table and test table (I don't like this approach because I'd have to duplicate the SWITCH across all measures)

DEFINE TABLE test_table = UNION(ROW("order","A1"),ROW("order","B1")) MEASURE 'Measures Table'[COUNT_ORDERS] = SWITCH(2, 1,DISTINCTCOUNT('actual_table'[order]), 2,DISTINCTCOUNT('test_table'[order]) ) EVALUATE ROW("COUNT_ORDERS",'Measures Table'[COUNT_ORDERS])

Do you have any alternative recommendations? Your help is greatly appreciated.

Re: Automated Unit Testing

lbendlin — Sat, 18 Nov 2023 18:25:47 GMT

Have your test data be part of the regular data source refreshes. For production use a filter that excludes the test data. For unit testing flip the filter to exclude the production data.

topic Automated Unit Testing in DAX Commands and Tips

Automated Unit Testing

Re: Automated Unit Testing