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.
