nerdy

Visualize dependencies between your DAX measures: DAX-VizArt-Wizard

by

This DAX-VizArt-Wizard vizualizes dependencies between your DAX measures, shows the definition of all related measures and shows differences between the measures of 2 models/versions. This works for Power Pivot, Power BI and for Analysis Services Tabular (SSAS).

In the Power BI-Version you’ll see them in the Sankey-chart like this:

VizArt1

If select measures/nodes, all direct connections will be highlighted:

VizArt2Direct

In the second version, all indirect connections will be highlighted as well & the selected measure definitions will be shown.

Read more

How to auto-parametrize your Power Query queries

by

This is about a technique that I’m going to use in my upcoming articles on ICT reconciliation. But as it is useful in other areas as well, it’s getting its own post here:

Say we want to pass the year and month as well as the type of accounts as filters/parameters into our reconciliation query. If our file is stored here:

C:\Users\Imke\Desktop\2015\08\\ICTRec201508_Transactions.xlsx,

we have everything we need. And when the next month comes, we even don’t have to adjust our query, as it will automatically take 09 as the months parameter, providing we store it in the correct folder.

Using: CELL(“filename”) will extract this information into the cell in Excel. Check this cell and pass it to Power Query (as table).

Image1

Now we just have to extract the relevant parts, using an ultracool ninja-trick I just picked up in the TechNet Forum:

Read more

Power Query Management Studio reloaded: Now supports MDX

by

Very happy to see that my Power Query Management Studio isn’t just perceived nerdy but useful as well 🙂 Thanks to Dusty for his nice review.

So let’s push it a bit further and add some MDX functions to it that cannot be done by DMVs:

  • get a list of all unique fields used in a specific MDX query
  • translate your code to a different cube using a simple field-translation table

How to use this for MDX:

Read more