If you’re monitoring activity in Power BI, for example using Log Analytics, you’ll know that you can capture the DAX queries generated by your published Power BI reports. How do you make sense of them though? You may want to know which tables, columns or measures are being used by a query – maybe so you can work out which ones aren’t being used and can be deleted. I always thought the only way to do this would be to parse the DAX query, which would be incredibly difficult to do. Yesterday, though, Igor Cotruta pointed out on Twitter that there’s a really easy way to do this that I didn’t know about, using the DISCOVER_CALC_DEPENDENCY DMV. I’ve used this DMV before and blogged about it here, but what was new to me is that you can pass a DAX query into the QUERY restriction (it is all documented here). To see how it works let’s look at a simple example.
Consider the following dataset consisting of a single table with a calculated column and a few measures:
Now consider a table visual built on this dataset with a slicer linked to it:
You can get the DAX query generated by this visual using Performance Analyzer’s Copy Query feature (described in this article). You can then pass this DAX query into the WHERE clause of a query against the DISCOVER_CALC_DEPENDENCY DMV like so:
SELECT
REFERENCED_OBJECT_TYPE,
REFERENCED_TABLE,
REFERENCED_OBJECT,
REFERENCED_EXPRESSION
FROM
$SYSTEM.DISCOVER_CALC_DEPENDENCY
WHERE
QUERY =
'
DEFINE
VAR __DS0FilterTable =
TREATAS({"Apples"}, ''Sales''[Product])
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(''Sales''[Country], "IsGrandTotalRowTotal"),
__DS0FilterTable,
"Sales_Amount", ''Sales''[Sales Amount],
"Effective_Tax_Rate", ''Sales''[Effective Tax Rate]
)
VAR __DS0PrimaryWindowed =
TOPN(502, __DS0Core, [IsGrandTotalRowTotal], 0, ''Sales''[Country], 1)
EVALUATE
__DS0PrimaryWindowed
ORDER BY
[IsGrandTotalRowTotal] DESC, ''Sales''[Country]
'
[Note that the DAX query I’m passing in contains single quotes that have to be escaped, which explains why all the table names are surrounded by two single quotes]
The query can be run from DAX Studio connected to either Power BI Desktop or a dataset published to Power BI Premium via the XMLA Endpoint. Here’s the output:
As you can see, this gives you the table, columns (including the hidden RowNumber column), measures and calculated column directly referenced by the query as well as the DAX definitions of the measures and calculated column. It does not mention the [Tax Amount] measure, which the [Effective Tax Rate] measure references; you can get the full list of measures and calculated columns in the dataset and the dependencies between them by querying the DMV without the WHERE clause though. Neither does the output tell you that the Product column is being sliced by the value “Apples”, which would also be useful. Overall, though, I think this is a really powerful feature.
This is a great tip! Another path to get similar information is with the “Measure Killer” External Tool (see https://en.brunner.bi/measurekiller). You open the tool from Power BI, click Run, and then save to an Excel file. The file lists all columns and measures, which are used, and will tell you what’s used by another column/measure in a calculation, or in a visual, filter, and even conditional formatting. It doesn’t list the EXPRESSION, but I’m hoping they’ll add it.
Version 0.8 of Measure Killer has EXPRESSION in the report. It is very useful and more reliable than using QUERY as shown in Jorge’s example below.
It happens that it does not cover other important dependencies also.
If I’ve a measure like:
Sales Amount TST =
CALCULATE(
[Sales Amount],
ALL( ‘Product'[Brand] )
)
, and run the DMV:
select * from $SYSTEM.DISCOVER_CALC_DEPENDENCY
where QUERY =
‘
// DAX Query
EVALUATE
ROW(
“Sales_Amount_TST”, ”Sales”[Sales Amount TST]
)
‘
,or even:
select * from $SYSTEM.DISCOVER_CALC_DEPENDENCY
where QUERY =
‘
// DAX Query
EVALUATE
ROW(
“Sales_Amount_TST”, CALCULATE(
[Sales Amount],
ALL( ”Product”[Brand] )
)
)
It does not show any dependency on column ‘Product'[Brand], but I should not remove that column from my model or my measure will not work anymore.
Jorge, I agree with your findings and I’m inclined to forget about this QUERY hack based on your example. I ran your test through the “Measure Killer 0.8” External Tool, and it does identify Product[Brand] as in use by Sales_Amount_TST, so Measure Killer is a better approach. It doesn’t show nested dependencies tied to Sales_Amount_TST, like the columns/measures used by [Sales Amount], but those columns/measures show up in use by [Sales Amount] separately. If you want the dependency chain for a measure, you can build it yourself in a Power Query. I have built these before and have some chains 14 levels deep.
In the example above, I could achieve the missing dependency using:
select * from $SYSTEM.DISCOVER_CALC_DEPENDENCY
where OBJECT=’Sales Amount TST’
but for report-level measures, I couldn’t because these measures don’t exist as part of the model.
@Jorge, there are data dependencies and named dependencies, did you try both? Is it missing w/ both restrictions? I don’t belive that can be the case.
select * from systemrestrictschema($system.discover_calc_dependency, [KIND] = ‘NAMED_DEPENDENCY’)
select * from systemrestrictschema($system.discover_calc_dependency, [KIND] = ‘DATA_DEPENDENCY’)
I’ve tried both.
Neither DATA_DEPENDENCY nor NAMED_DEPENDENCY identifies any dependency on Product[Brand]
select * from systemrestrictschema($system.DISCOVER_CALC_DEPENDENCY, [KIND] = ‘DATA_DEPENDENCY’)
where QUERY = ‘
DEFINE MEASURE Sales[Sales Amount TST_2] =
CALCULATE(
[Sales Amount],
ALL( Product[Brand] )
)
EVALUATE
ROW(
“Sales Amount TST_2”, Sales[Sales Amount TST_2]
)
‘
OR
select * from systemrestrictschema($system.DISCOVER_CALC_DEPENDENCY, [KIND] = ‘DATA_DEPENDENCY’)
where QUERY = ‘
DEFINE MEASURE Sales[Sales Amount TST_2] =
CALCULATE(
[Sales Amount],
ALL( Product[Brand] )
)
EVALUATE
ROW(
“Sales Amount TST_2”, Sales[Sales Amount TST_2]
)
‘
But have a try, please.
NOTE: Just to fix a typo in the 2nd DMV query example above
I’ve tried both.
Neither DATA_DEPENDENCY nor NAMED_DEPENDENCY identifies any dependency on Product[Brand]
select * from systemrestrictschema($system.DISCOVER_CALC_DEPENDENCY, [KIND] = ‘DATA_DEPENDENCY’)
where QUERY = ‘
DEFINE MEASURE Sales[Sales Amount TST_2] =
CALCULATE(
[Sales Amount],
ALL( Product[Brand] )
)
EVALUATE
ROW(
“Sales Amount TST_2”, Sales[Sales Amount TST_2]
)
‘
OR
select * from systemrestrictschema($system.DISCOVER_CALC_DEPENDENCY, [KIND] = ‘NAMED_DEPENDENCY’)
where QUERY = ‘
DEFINE MEASURE Sales[Sales Amount TST_2] =
CALCULATE(
[Sales Amount],
ALL( Product[Brand] )
)
EVALUATE
ROW(
“Sales Amount TST_2”, Sales[Sales Amount TST_2]
)
‘
But have a try, please.
Wonderfull!
Can you do something similary with a DAX expression (measures/calc columns)?